package com.apes.fn.scm.retailOrder.service.tc;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import org.springframework.stereotype.Component;

/**
 * Creator: zouyc
 * CreateDate: 2020-09-07 19:40
 * Description:
 **/
@Component
public class TCDao extends ApesDao {

    /**
     * 取500条数据
     *
     * @param param
     * @return
     */
    public JSONArray queryTCRYSGTC(JSONObject param) {
        String sSQL = " SELECT * FROM (  SELECT A.*,"
                + " (SELECT Z.NAME FROM MAS_DEPT Z WHERE Z.ID = A.DEPT_ID AND ROWNUM = 1) DEPT_NAME, "
                + " (SELECT O.NAME FROM MAS_PARTY O, MAS_PARTY_ROLE P WHERE O.ID = P.PARTY_ID AND P.ID = A.WORKER AND ROWNUM = 1) WORKER_NAME, "
                + " (SELECT O.NAME FROM MAS_PARTY O, MAS_PARTY_ROLE P WHERE O.ID = P.PARTY_ID AND P.ID = A.COMPANY_ID AND ROWNUM = 1) COMPANY_NAME, "
                + " (SELECT SUM(Z.TC_AMT) FROM TC_RYSGTC Z WHERE Z.PRODUCT_ID = A.PRODUCT_ID AND Z.RETAIL_ORDER_ID = A.RETAIL_ORDER_ID) SUMTC, "
                + " (SELECT 1 / COUNT(*) SUMPORTION FROM TC_RYSGTC Z  WHERE Z.PRODUCT_ID = A.PRODUCT_ID AND Z.RETAIL_ORDER_ID = A.RETAIL_ORDER_ID) WASH_AMOUNT, "
                + " (SELECT A.SALE_AMT / SUM(Z.PORTION) * A.PORTION SUMPORTION FROM TC_RYSGTC Z WHERE Z.PRODUCT_ID = A.PRODUCT_ID AND Z.RETAIL_ORDER_ID = A.RETAIL_ORDER_ID) PORTIONSUM  "
                + " FROM TC_RYSGTC A "
                + " WHERE A.ID > :ID  ORDER BY A.ID ) WHERE ROWNUM <= 2000 ";
        return toJSONArray(find(sSQL, param));
    }


    /**
     * 取500条数据
     *
     * @param param
     * @return
     */
    public JSONArray queryTCRYSGTCJSC(JSONObject param) {
        String sSQL = " SELECT * FROM ( SELECT A.*,"
                + " (SELECT Z.NAME FROM MAS_DEPT Z WHERE Z.ID = A.DISPATCHER_DEPT_ID AND ROWNUM = 1) DISPATCHER_DEPT_NAME, "
                + " (SELECT O.NAME FROM MAS_PARTY O, MAS_PARTY_ROLE P WHERE O.ID = P.PARTY_ID AND P.ID = A.JSCR AND ROWNUM = 1) JSCR_NAME, "
                + " (SELECT O.NAME FROM MAS_PARTY O, MAS_PARTY_ROLE P WHERE O.ID = P.PARTY_ID AND P.ID = A.COMPANY_ID AND ROWNUM = 1) COMPANY_NAME "
                + " FROM TC_JSCTC A WHERE A.ID > :ID  ORDER BY A.ID ) WHERE ROWNUM <= 2000 ";
        return toJSONArray(find(sSQL, param));
    }

    /**
     * 取500条数据
     *
     * @param param
     * @return
     */
    public JSONArray queryTCRYSGTCKD(JSONObject param) {
        String sSQL = " SELECT * FROM ( SELECT A.*, "
                + " (SELECT Z.NAME FROM MAS_DEPT Z WHERE Z.ID = A.DEPT_ID AND ROWNUM = 1) DEPT_NAME, "
                + " (SELECT O.NAME FROM MAS_PARTY O, MAS_PARTY_ROLE P WHERE O.ID = P.PARTY_ID AND P.ID = A.BILL_MAN AND ROWNUM = 1) BILL_MAN_NAME, "
                + " (SELECT O.NAME FROM MAS_PARTY O, MAS_PARTY_ROLE P WHERE O.ID = P.PARTY_ID AND P.ID = A.COMPANY_ID AND ROWNUM = 1) COMPANY_NAME "
                + " FROM TC_KDTC A WHERE TO_NUMBER(A.ID) > :ID AND A.BILLING_TYPE IN ('002286', '002285')  ORDER BY A.ID ) WHERE ROWNUM <= 2000 ";
        return toJSONArray(find(sSQL, param));
    }

    /**
     * 取年卡提成1500条数据
     *
     * @param param
     * @return
     */
    public JSONArray queryTCNKTC(JSONObject param) {
        String sSQL = "  SELECT * FROM (SELECT A.ID,A.TJRQ,A.COMPANY_ID,\n" +
                "                        (SELECT T.NAME FROM MAS_PARTY T, MAS_PARTY_ROLE A WHERE A.PARTY_ID = T.ID AND A.ID = A.COMPANY_ID) COMPANY_NAME,\n" +
                "                        A.DEPT_ID,\n" +
                "                        (SELECT T.NAME FROM MAS_DEPT T WHERE T.ID = A.DEPT_ID) DEPT_NAME,\n" +
                "                        A.SALESMAN EMPLOYEE_ID,\n" +
                "                        (SELECT T.NAME FROM MAS_PARTY T, MAS_PARTY_ROLE A WHERE A.PARTY_ID = T.ID AND A.ID = A.SALESMAN) EMPLOYEE_NAME,\n" +
                "                        A.SALE_AMT,\n" +
                "                        A.TC_AMT TCJE,\n" +
                "                        'NKTC' ORDER_CLASSIFY\n" +
                "                   FROM TC_NKTC A\n" +
                "                  WHERE A.ID > :ID\n" +
                "                    AND A.SALESMAN IS NOT NULL\n" +
                "                  ORDER BY A.ID)\n" +
                "                  WHERE ROWNUM <= 1500";
        return toJSONArray(find(sSQL, param));
    }

    /**
     * 取销售提成500条数据
     *
     * @param param
     * @return
     */
    public JSONArray queryTCXSTC(JSONObject param) {
        String sSQL = " SELECT * FROM (SELECT B.ID ,B.TJRQ,B.COMPANY_ID,\n" +
                "              (SELECT T.NAME FROM MAS_PARTY T, MAS_PARTY_ROLE A WHERE A.PARTY_ID = T.ID AND A.ID = B.COMPANY_ID) COMPANY_NAME,\n" +
                "              B.DEPT_ID, (SELECT T.NAME FROM MAS_DEPT T WHERE T.ID = B.DEPT_ID) DEPT_NAME, B.MAIN_SALESMAN EMPLOYEE_ID,\n" +
                "              (SELECT T.NAME FROM MAS_PARTY T, MAS_PARTY_ROLE A WHERE A.PARTY_ID = T.ID AND A.ID = B.MAIN_SALESMAN) EMPLOYEE_NAME,\n" +
                "              B.SERVE_TYPE_ID,B.SALE_AMT, B.TCJE,'XSTC' ORDER_CLASSIFY\n" +
                "              FROM TC_XSETC B WHERE B.ID > :ID ORDER BY B.ID ) WHERE ROWNUM <= 2000 ";
        return toJSONArray(find(sSQL, param));
    }

    /**
     * 取定损销售提成500条数据
     *
     * @param param
     * @return
     */
    public JSONArray queryTCDSXSETC(JSONObject param) {
        String sSQL = " SELECT * FROM (SELECT C.ID,C.TJRQ,C.COMPANY_ID, C.GET_CAR_UID EMPLOYEE_ID,\n" +
                "              (SELECT T.NAME FROM MAS_PARTY T, MAS_PARTY_ROLE A WHERE A.PARTY_ID = T.ID AND A.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "              C.DEPT_ID,(SELECT T.NAME FROM MAS_DEPT T WHERE T.ID = C.DEPT_ID) DEPT_NAME,\n" +
                "              (SELECT T.NAME FROM MAS_PARTY T, MAS_PARTY_ROLE A WHERE A.PARTY_ID = T.ID AND A.ID = C.GET_CAR_UID) EMPLOYEE_NAME,\n" +
                "              NULL SERVE_TYPE_ID, C.AMOUNT SALE_AMT,C.LOSS_QUOTATION_AMT,C.TCJE,'DSXS' ORDER_CLASSIFY\n" +
                "              FROM TC_DSXSETC C WHERE C.ID > :ID ORDER BY C.ID )WHERE ROWNUM <= 1500 ";
        return toJSONArray(find(sSQL, param));
    }

    /**
     * 取车险销售提成500条数据
     *
     * @param param
     * @return
     */
    public JSONArray queryTCCXTC(JSONObject param) {
        String sSQL = " SELECT * FROM (SELECT D.ID,D.TJRQ,D.COMPANY_ID,D.DEPT_ID, D.SALESMAN_ID EMPLOYEE_ID,\n" +
                "              (SELECT T.NAME FROM MAS_PARTY T, MAS_PARTY_ROLE A WHERE A.PARTY_ID = T.ID AND A.ID = D.COMPANY_ID) COMPANY_NAME,\n" +
                "              (SELECT T.NAME FROM MAS_DEPT T WHERE T.ID = D.DEPT_ID) DEPT_NAME,\n" +
                "              (SELECT T.NAME FROM MAS_PARTY T, MAS_PARTY_ROLE A WHERE A.PARTY_ID = T.ID  AND A.ID = D.SALESMAN_ID) EMPLOYEE_NAME,\n" +
                "              NULL SERVE_TYPE_ID,  D.NET_PREMIUM NET_PREMIUM,d.FN_COMMISSION,d.CXJYE, D.FN_PROFIT TCJE,  'CXTC' ORDER_CLASSIFY\n" +
                "              FROM TC_CXTC D WHERE D.ID > :ID ORDER BY D.ID) WHERE ROWNUM <= 1500 ";
        return toJSONArray(find(sSQL, param));
    }


    public JSONArray queryRYTCForDay(JSONObject param) {
        String sSQL = " SELECT SUM(A.TC_TOTAL) TC_TOTAL,SUM(A.SGTC + A.KDTC + A.JSCTC)SGTC, \n" +
                " SUM(A.XSTC + A.DSXSTC + A.NKTC + A.KDTCXS +A.CXTC + A.DSGZTC + A.DSFWTC)XSTC \n" +
                " FROM TC_RYTCRBB A WHERE A.PERSON_ID =:PERSON \n" +
                "    AND A.TJRQ >=TRUNC(:REQUEST_DATE) AND A.TJRQ < TRUNC(:REQUEST_DATE) +1   ";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray queryRYTCForMonth(JSONObject param) {
        String sSQL = " SELECT SUM(A.TC_TOTAL)TC_TOTAL,SUM(A.SGTC + A.KDTC + A.JSCTC)SGTC, " +
                " SUM(A.XSTC + A.DSXSTC + A.NKTC + A.KDTCXS +A.CXTC + A.DSGZTC + A.DSFWTC)XSTC \n" +
                "FROM  TC_RYTCRBB A  WHERE A.PERSON_ID =:PERSON AND TO_CHAR(A.TJRQ,'YYYYMM') =:TJRQ  ";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray queryMDTCForDay(JSONObject param) {
        String sSQL = " SELECT SUM(A.TC_TOTAL)TC_TOTAL,SUM(A.SGTC + A.KDTC + A.JSCTC)SGTC, " +
                " SUM(A.XSTC + A.DSXSTC + A.NKTC + A.KDTCXS +A.CXTC + A.DSGZTC + A.DSFWTC)XSTC \n" +
                "FROM  TC_RYTCRBB A" +
                " WHERE A.DEPT_ID =:DEPT_ID AND A.TJRQ >=TRUNC(:REQUEST_DATE) AND A.TJRQ < TRUNC(:REQUEST_DATE) +1   ";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray queryMDTCForMonth(JSONObject param) {
        String sSQL = " SELECT SUM(A.TC_TOTAL)TC_TOTAL,SUM(A.SGTC + A.KDTC + A.JSCTC)SGTC," +
                " SUM(A.XSTC + A.DSXSTC + A.NKTC + A.KDTCXS +A.CXTC + A.DSGZTC + A.DSFWTC)XSTC \n" +
                " FROM  TC_RYTCRBB A  WHERE A.DEPT_ID =:DEPT_ID AND TO_CHAR(A.TJRQ,'YYYYMM') =:TJRQ  ";
        return toJSONArray(find(sSQL, param));
    }

    /**
     * 查询个人销售提成
     *
     * @param param
     * @return
     */
    public JSONArray querySalePercentage(JSONObject param) {
        String sSQL = " SELECT A.RETAIL_ORDER_ID ORDER_NUMBER,A.PLATE_LICENSE,D.CAR_BRAND LABEL_NAME,F.NAME MODEL_NAME,A.CREATE_DATE TJRQ,A.MAIN_SALESMAN_TCJE TCJE,\n" +
                "      (SELECT NAME FROM MAS_PRODUCT B WHERE A.PRODUCT_ID = B.ID)PRODUCT_NAME,A.SALE_QTY, " +
                "      (SELECT NAME FROM HR_EMPLOYEE WHERE ORIGIN = A.MAIN_SALESMAN) SALESMAN_NAME " +
                "FROM TC_XSETC A ,MAS_CAR_CONFIGURATION C,MAS_CAR_LABEL D,MAS_CAR_MODEL F,\n" +
                "       ( SELECT B.ID,B.JEKUN_MODEL_CODE,B.CAR_LICENSE FROM FN_CUSTOMER_EXPAND B,\n" +
                "          (SELECT N.CAR_LICENSE,MAX(N.CREATE_DATE)CREATE_DATE FROM FN_CUSTOMER_EXPAND N GROUP BY N.CAR_LICENSE) G\n" +
                "         WHERE B.CREATE_DATE = G.CREATE_DATE AND B.CAR_LICENSE = G.CAR_LICENSE) B\n" +
                "WHERE A.MAIN_SALESMAN =:PERSON  AND TRUNC(A.CREATE_DATE) = TRUNC(:REQUEST_DATE) \n" +
                "      AND A.PLATE_LICENSE = B.CAR_LICENSE AND B.JEKUN_MODEL_CODE = C.ID AND C.CAR_LABEL_ID = D.ID \n" +
                "      AND C.CAR_MODEL_ID = F.ID   " +
                "UNION ALL \n" +
                "SELECT A.RETAIL_ORDER_ID ORDER_NUMBER,A.PLATE_LICENSE,D.CAR_BRAND LABEL_NAME,F.NAME MODEL_NAME,A.CREATE_DATE TJRQ,A.SUB_SALESMAN_TCJE TCJE,\n" +
                "      (SELECT NAME FROM MAS_PRODUCT B WHERE A.PRODUCT_ID = B.ID)PRODUCT_NAME,A.SALE_QTY, " +
                "      (SELECT NAME FROM HR_EMPLOYEE WHERE ORIGIN = A.SUB_SALESMAN) SALESMAN_NAME " +
                "FROM TC_XSETC A ,MAS_CAR_CONFIGURATION C,MAS_CAR_LABEL D,MAS_CAR_MODEL F,\n" +
                "       ( SELECT B.ID,B.JEKUN_MODEL_CODE,B.CAR_LICENSE FROM FN_CUSTOMER_EXPAND B,\n" +
                "          (SELECT N.CAR_LICENSE,MAX(N.CREATE_DATE)CREATE_DATE FROM FN_CUSTOMER_EXPAND N GROUP BY N.CAR_LICENSE) G\n" +
                "         WHERE B.CREATE_DATE = G.CREATE_DATE AND B.CAR_LICENSE = G.CAR_LICENSE) B\n" +
                "WHERE A.SUB_SALESMAN =:PERSON AND TRUNC(A.CREATE_DATE) = TRUNC(:REQUEST_DATE) \n" +
                "      AND A.PLATE_LICENSE = B.CAR_LICENSE AND B.JEKUN_MODEL_CODE = C.ID AND C.CAR_LABEL_ID = D.ID \n" +
                "      AND C.CAR_MODEL_ID = F.ID ";
        return toJSONArray(find(sSQL, param));
    }

    /**
     * 查询个人销售提成
     *
     * @param param
     * @return
     */
    public JSONArray querySalePercentageForMonth(JSONObject param) {
        String sSQL = " SELECT A.RETAIL_ORDER_ID ORDER_NUMBER,A.PLATE_LICENSE,D.CAR_BRAND LABEL_NAME,F.NAME MODEL_NAME,TRUNC(A.CREATE_DATE)TJRQ," +
                "     A.MAIN_SALESMAN_TCJE TCJE,\n" +
                "      (SELECT NAME FROM MAS_PRODUCT B WHERE A.PRODUCT_ID = B.ID)PRODUCT_NAME,A.SALE_QTY, " +
                "      (SELECT NAME FROM HR_EMPLOYEE WHERE ORIGIN = A.MAIN_SALESMAN) SALESMAN_NAME " +
                "FROM TC_XSETC A ,MAS_CAR_CONFIGURATION C,MAS_CAR_LABEL D,MAS_CAR_MODEL F,\n" +
                "       ( SELECT B.ID,B.JEKUN_MODEL_CODE,B.CAR_LICENSE FROM FN_CUSTOMER_EXPAND B,\n" +
                "          (SELECT N.CAR_LICENSE,MAX(N.CREATE_DATE)CREATE_DATE FROM FN_CUSTOMER_EXPAND N GROUP BY N.CAR_LICENSE) G\n" +
                "         WHERE B.CREATE_DATE = G.CREATE_DATE AND B.CAR_LICENSE = G.CAR_LICENSE) B\n" +
                "WHERE A.MAIN_SALESMAN =:PERSON  AND TO_CHAR(A.CREATE_DATE,'YYYYMM') =:TJRQ \n" +
                "      AND A.PLATE_LICENSE = B.CAR_LICENSE AND B.JEKUN_MODEL_CODE = C.ID AND C.CAR_LABEL_ID = D.ID \n" +
                "      AND C.CAR_MODEL_ID = F.ID   " +
                "UNION ALL \n" +
                "SELECT A.RETAIL_ORDER_ID ORDER_NUMBER,A.PLATE_LICENSE,D.CAR_BRAND LABEL_NAME,F.NAME MODEL_NAME,A.CREATE_DATE TJRQ,A.SUB_SALESMAN_TCJE TCJE,\n" +
                "      (SELECT NAME FROM MAS_PRODUCT B WHERE A.PRODUCT_ID = B.ID)PRODUCT_NAME,A.SALE_QTY, " +
                "      (SELECT NAME FROM HR_EMPLOYEE WHERE ORIGIN = A.SUB_SALESMAN) SALESMAN_NAME " +
                "FROM TC_XSETC A ,MAS_CAR_CONFIGURATION C,MAS_CAR_LABEL D,MAS_CAR_MODEL F,\n" +
                "       ( SELECT B.ID,B.JEKUN_MODEL_CODE,B.CAR_LICENSE FROM FN_CUSTOMER_EXPAND B,\n" +
                "          (SELECT N.CAR_LICENSE,MAX(N.CREATE_DATE)CREATE_DATE FROM FN_CUSTOMER_EXPAND N GROUP BY N.CAR_LICENSE) G\n" +
                "         WHERE B.CREATE_DATE = G.CREATE_DATE AND B.CAR_LICENSE = G.CAR_LICENSE) B\n" +
                "WHERE A.SUB_SALESMAN =:PERSON AND TO_CHAR(A.CREATE_DATE,'YYYYMM') =:TJRQ \n" +
                "      AND A.PLATE_LICENSE = B.CAR_LICENSE AND B.JEKUN_MODEL_CODE = C.ID AND C.CAR_LABEL_ID = D.ID \n" +
                "      AND C.CAR_MODEL_ID = F.ID ";
        return toJSONArray(find(sSQL, param));
    }

    //个人年卡提成
    public JSONArray queryNKTCForDay(JSONObject param) {
        String sSQL = "SELECT T.ORDER_NUMBER ORDER_NUMBER, T.CREATE_DATE TJRQ, SUM(T.TCJE) TCJE, T.SALESMAN_NAME SALESMAN_NAME " +
                "FROM (SELECT A.RETAIL_ORDER_ID ORDER_NUMBER, A.CREATE_DATE, " +
                "        A.MAIN_SALESMAN_TCJE TCJE, " +
                "        (SELECT NAME FROM HR_EMPLOYEE WHERE ORIGIN = A.SALESMAN) SALESMAN_NAME " +
                "FROM TC_NKTC A " +
                "WHERE A.SALESMAN = :PERSON " +
                "AND TRUNC(A.CREATE_DATE) = TRUNC(:REQUEST_DATE) " +
                "UNION ALL " +
                "SELECT A.RETAIL_ORDER_ID ORDER_NUMBER, A.CREATE_DATE, " +
                "        A.SUB_SALESMAN_TCJE TCJE," +
                "        (SELECT NAME FROM HR_EMPLOYEE WHERE ORIGIN = A.SUB_SALESMAN) SALESMAN_NAME " +
                "FROM TC_NKTC A " +
                "WHERE A.SUB_SALESMAN = :PERSON " +
                "AND TRUNC(A.CREATE_DATE) = TRUNC(:REQUEST_DATE)) T " +
                "GROUP BY T.ORDER_NUMBER, T.CREATE_DATE, T.SALESMAN_NAME ";
        return toJSONArray(find(sSQL, param));
    }

    //个人年卡提成
    public JSONArray queryNKTCForMonth(JSONObject param) {
        String sSQL = "SELECT T.ORDER_NUMBER ORDER_NUMBER, T.CREATE_DATE TJRQ, SUM(T.TCJE) TCJE, T.SALESMAN_NAME SALESMAN_NAME " +
                "FROM (SELECT A.RETAIL_ORDER_ID ORDER_NUMBER, A.CREATE_DATE, " +
                "        A.MAIN_SALESMAN_TCJE TCJE, " +
                "        (SELECT NAME FROM HR_EMPLOYEE WHERE ORIGIN = A.SALESMAN) SALESMAN_NAME " +
                "FROM TC_NKTC A " +
                "WHERE A.SALESMAN = :PERSON " +
                "AND TO_CHAR(A.CREATE_DATE, 'YYYYMM') = :TJRQ " +
                "UNION ALL " +
                "SELECT A.RETAIL_ORDER_ID ORDER_NUMBER, A.CREATE_DATE, " +
                "        A.SUB_SALESMAN_TCJE TCJE, " +
                "        (SELECT NAME FROM HR_EMPLOYEE WHERE ORIGIN = A.SUB_SALESMAN) SALESMAN_NAME " +
                "FROM TC_NKTC A " +
                "WHERE A.SUB_SALESMAN = :PERSON " +
                "AND TO_CHAR(A.CREATE_DATE, 'YYYYMM') = :TJRQ) T " +
                "GROUP BY T.ORDER_NUMBER, T.CREATE_DATE, T.SALESMAN_NAME ";
        return toJSONArray(find(sSQL, param));
    }

    //个人车险提成
    public JSONArray queryCXTCForDay(JSONObject param) {
        String sSQL = "SELECT A.ID ORDER_NUMBER, A.PLATE_LICENSE, A.CREATE_DATE TJRQ,    " +
                "       A.FN_PROFIT TCJE, D.CAR_BRAND CAR_MODEL, F.NAME,    " +
                "       (SELECT NAME FROM HR_EMPLOYEE WHERE ORIGIN = A.SALESMAN_ID) SALESMAN_NAME    " +
                "  FROM TC_CXTC A, MAS_CAR_CONFIGURATION C, MAS_CAR_LABEL D, MAS_CAR_MODEL F,    " +
                "       (SELECT B.ID, B.JEKUN_MODEL_CODE, B.CAR_LICENSE    " +
                "           FROM FN_CUSTOMER_EXPAND B,    " +
                "                (SELECT N.CAR_LICENSE, MAX(N.CREATE_DATE) CREATE_DATE    " +
                "                    FROM FN_CUSTOMER_EXPAND N    " +
                "                   GROUP BY N.CAR_LICENSE) G    " +
                "          WHERE B.CREATE_DATE = G.CREATE_DATE    " +
                "            AND B.CAR_LICENSE = G.CAR_LICENSE) B    " +
                " WHERE A.PLATE_LICENSE = B.CAR_LICENSE    " +
                "   AND B.JEKUN_MODEL_CODE = C.ID    " +
                "   AND C.CAR_LABEL_ID = D.ID    " +
                "   AND C.CAR_MODEL_ID = F.ID    " +
                "   AND A.SALESMAN_ID = :PERSON  " +
                "   AND TRUNC(A.CREATE_DATE) = :REQUEST_DATE  " +
                " ORDER BY A.CREATE_DATE DESC";
        return toJSONArray(find(sSQL, param));
    }

    //个人车险提成
    public JSONArray queryCXTCForMonth(JSONObject param) {
        String sSQL = "SELECT A.ID ORDER_NUMBER, A.PLATE_LICENSE, A.CREATE_DATE TJRQ, A.FN_PROFIT TCJE,\n" +
                "       D.CAR_BRAND CAR_MODEL,F.NAME ,\n" +
                "       (SELECT NAME FROM HR_EMPLOYEE WHERE ORIGIN = A.SALESMAN_ID) SALESMAN_NAME\n" +
                "FROM TC_CXTC A " +
                " LEFT JOIN  " +
                "   (SELECT B.ID,B.JEKUN_MODEL_CODE,B.CAR_LICENSE,ROW_NUMBER() OVER(PARTITION BY b.car_license ORDER BY b.create_date DESC) NUM FROM FN_CUSTOMER_EXPAND B)B " +
                " ON A.PLATE_LICENSE = B.CAR_LICENSE AND B.NUM = 1" +
                "LEFT JOIN MAS_CAR_CONFIGURATION C\n" +
                "ON B.JEKUN_MODEL_CODE = C.ID\n" +
                "LEFT JOIN MAS_CAR_LABEL D\n" +
                "ON C.CAR_LABEL_ID = D.ID\n" +
                "LEFT JOIN MAS_CAR_MODEL F\n" +
                "ON C.CAR_MODEL_ID = F.ID      " +
                " WHERE A.SALESMAN_ID =:PERSON  AND TO_CHAR(A.CREATE_DATE,'YYYYMM') =:TJRQ " +
                " ORDER BY A.CREATE_DATE DESC ";
        return toJSONArray(find(sSQL, param));
    }

    //个人开单提成
    public JSONArray queryKDTCForDay(JSONObject param) {
        String sSQL = "SELECT A.FN_INNER_PICK_DEL_UP_CAR_ID ORDER_NUMBER,A.PLATE_LICENSE,D.CAR_BRAND LABEL_NAME,F.NAME MODEL_NAME,A.CREATE_DATE TJRQ,A.TC_AMT TCJE,  \n" +
                "      (SELECT NAME FROM HR_EMPLOYEE WHERE ORIGIN = A.BILL_MAN) SALESMAN_NAME " +
                "FROM TC_KDTC A,MAS_CAR_CONFIGURATION C,MAS_CAR_LABEL D,MAS_CAR_MODEL F,\n" +
                "       ( SELECT B.ID,B.JEKUN_MODEL_CODE,B.CAR_LICENSE FROM FN_CUSTOMER_EXPAND B,\n" +
                "          (SELECT N.CAR_LICENSE,MAX(N.CREATE_DATE)CREATE_DATE FROM FN_CUSTOMER_EXPAND N GROUP BY N.CAR_LICENSE) G\n" +
                "         WHERE B.CREATE_DATE = G.CREATE_DATE AND B.CAR_LICENSE = G.CAR_LICENSE) B\n" +
                "WHERE A.BILL_MAN =:PERSON  AND TRUNC(A.CREATE_DATE) = TRUNC(:REQUEST_DATE) \n" +
                "      AND A.PLATE_LICENSE = B.CAR_LICENSE AND B.JEKUN_MODEL_CODE = C.ID \n" +
                "      AND C.CAR_LABEL_ID = D.ID AND C.CAR_MODEL_ID = F.ID  ORDER BY A.CREATE_DATE DESC ";
        return toJSONArray(find(sSQL, param));
    }

    //个人开单提成
    public JSONArray queryKDTCForMonth(JSONObject param) {
        String sSQL = "SELECT A.FN_INNER_PICK_DEL_UP_CAR_ID ORDER_NUMBER,A.PLATE_LICENSE,D.CAR_BRAND LABEL_NAME,F.NAME MODEL_NAME,TRUNC(A.CREATE_DATE)TJRQ,A.TC_AMT TCJE,  \n" +
                "      (SELECT NAME FROM HR_EMPLOYEE WHERE ORIGIN = A.BILL_MAN) SALESMAN_NAME " +
                "FROM TC_KDTC A,MAS_CAR_CONFIGURATION C,MAS_CAR_LABEL D,MAS_CAR_MODEL F,\n" +
                "       ( SELECT B.ID,B.JEKUN_MODEL_CODE,B.CAR_LICENSE FROM FN_CUSTOMER_EXPAND B,\n" +
                "          (SELECT N.CAR_LICENSE,MAX(N.CREATE_DATE)CREATE_DATE FROM FN_CUSTOMER_EXPAND N GROUP BY N.CAR_LICENSE) G\n" +
                "         WHERE B.CREATE_DATE = G.CREATE_DATE AND B.CAR_LICENSE = G.CAR_LICENSE) B\n" +
                "WHERE A.BILL_MAN =:PERSON AND TO_CHAR(A.CREATE_DATE,'YYYYMM') =:TJRQ \n" +
                "      AND A.PLATE_LICENSE = B.CAR_LICENSE AND B.JEKUN_MODEL_CODE = C.ID \n" +
                "      AND C.CAR_LABEL_ID = D.ID AND C.CAR_MODEL_ID = F.ID  ORDER BY A.CREATE_DATE DESC ";
        return toJSONArray(find(sSQL, param));
    }

    //个人接送车提成
    public JSONArray queryJSCTCForDay(JSONObject param) {
        String sSQL = "SELECT A.FN_INNER_PICK_DEL_UP_CAR_ID ORDER_NUMBER,A.PLATE_LICENSE,D.CAR_BRAND LABEL_NAME,F.NAME MODEL_NAME,A.CREATE_DATE TJRQ,A.TC_AMT TCJE,  \n" +
                "      (SELECT NAME FROM HR_EMPLOYEE WHERE ORIGIN = A.JSCR) SALESMAN_NAME " +
                "FROM TC_JSCTC A,MAS_CAR_CONFIGURATION C,MAS_CAR_LABEL D,MAS_CAR_MODEL F,\n" +
                "       ( SELECT B.ID,B.JEKUN_MODEL_CODE,B.CAR_LICENSE FROM FN_CUSTOMER_EXPAND B,\n" +
                "          (SELECT N.CAR_LICENSE,MAX(N.CREATE_DATE)CREATE_DATE FROM FN_CUSTOMER_EXPAND N GROUP BY N.CAR_LICENSE) G\n" +
                "         WHERE B.CREATE_DATE = G.CREATE_DATE AND B.CAR_LICENSE = G.CAR_LICENSE) B\n" +
                "WHERE A.JSCR =:PERSON  AND TRUNC(A.CREATE_DATE) = TRUNC(:REQUEST_DATE) \n" +
                "      AND A.PLATE_LICENSE = B.CAR_LICENSE AND B.JEKUN_MODEL_CODE = C.ID \n" +
                "      AND C.CAR_LABEL_ID = D.ID AND C.CAR_MODEL_ID = F.ID  ORDER BY A.CREATE_DATE DESC ";
        return toJSONArray(find(sSQL, param));
    }

    //个人接送车提成
    public JSONArray queryJSCTCForMonth(JSONObject param) {
        String sSQL = "SELECT A.FN_INNER_PICK_DEL_UP_CAR_ID ORDER_NUMBER,A.PLATE_LICENSE,D.CAR_BRAND LABEL_NAME," +
                " F.NAME MODEL_NAME,TRUNC(A.CREATE_DATE)TJRQ,A.TC_AMT TCJE,  \n" +
                "      (SELECT NAME FROM HR_EMPLOYEE WHERE ORIGIN = A.JSCR) SALESMAN_NAME " +
                "FROM TC_JSCTC A,MAS_CAR_CONFIGURATION C,MAS_CAR_LABEL D,MAS_CAR_MODEL F,\n" +
                "       ( SELECT B.ID,B.JEKUN_MODEL_CODE,B.CAR_LICENSE FROM FN_CUSTOMER_EXPAND B,\n" +
                "          (SELECT N.CAR_LICENSE,MAX(N.CREATE_DATE)CREATE_DATE FROM FN_CUSTOMER_EXPAND N GROUP BY N.CAR_LICENSE) G\n" +
                "         WHERE B.CREATE_DATE = G.CREATE_DATE AND B.CAR_LICENSE = G.CAR_LICENSE) B\n" +
                "WHERE A.JSCR =:PERSON AND TO_CHAR(A.CREATE_DATE,'YYYYMM') =:TJRQ \n" +
                "      AND A.PLATE_LICENSE = B.CAR_LICENSE AND B.JEKUN_MODEL_CODE = C.ID \n" +
                "      AND C.CAR_LABEL_ID = D.ID AND C.CAR_MODEL_ID = F.ID  ORDER BY A.CREATE_DATE DESC ";
        return toJSONArray(find(sSQL, param));
    }

    //个人接案介绍费提成
    public JSONArray queryJSFTCForDay(JSONObject param) {
        String sSQL = "SELECT A.SOURCE ORDER_NUMBER,A.PLATE_LICENSE,D.CAR_BRAND LABEL_NAME,F.NAME MODEL_NAME,A.CREATE_DATE TJRQ,A.TCJE , \n" +
                "      (SELECT NAME FROM HR_EMPLOYEE WHERE ORIGIN = A.GET_CAR_UID) SALESMAN_NAME " +
                "FROM TC_DSXSETC A,MAS_CAR_CONFIGURATION C,MAS_CAR_LABEL D,MAS_CAR_MODEL F,\n" +
                "       ( SELECT B.ID,B.JEKUN_MODEL_CODE,B.CAR_LICENSE FROM FN_CUSTOMER_EXPAND B,\n" +
                "          (SELECT N.CAR_LICENSE,MAX(N.CREATE_DATE)CREATE_DATE FROM FN_CUSTOMER_EXPAND N GROUP BY N.CAR_LICENSE) G\n" +
                "         WHERE B.CREATE_DATE = G.CREATE_DATE AND B.CAR_LICENSE = G.CAR_LICENSE) B\n" +
                "WHERE A.GET_CAR_UID =:PERSON  AND TRUNC(A.CREATE_DATE) = TRUNC(:REQUEST_DATE) \n" +
                "      AND A.PLATE_LICENSE = B.CAR_LICENSE AND B.JEKUN_MODEL_CODE = C.ID \n" +
                "      AND C.CAR_LABEL_ID = D.ID AND C.CAR_MODEL_ID = F.ID  ORDER BY A.CREATE_DATE DESC ";
        return toJSONArray(find(sSQL, param));
    }

    //个人接案介绍费提成
    public JSONArray queryJSFTCForMonth(JSONObject param) {
        String sSQL = "SELECT A.SOURCE ORDER_NUMBER,A.PLATE_LICENSE,D.CAR_BRAND LABEL_NAME,F.NAME MODEL_NAME,TRUNC(A.CREATE_DATE)TJRQ,A.TCJE , \n" +
                "      (SELECT NAME FROM HR_EMPLOYEE WHERE ORIGIN = A.GET_CAR_UID) SALESMAN_NAME " +
                "FROM TC_DSXSETC A,MAS_CAR_CONFIGURATION C,MAS_CAR_LABEL D,MAS_CAR_MODEL F,\n" +
                "       ( SELECT B.ID,B.JEKUN_MODEL_CODE,B.CAR_LICENSE FROM FN_CUSTOMER_EXPAND B,\n" +
                "          (SELECT N.CAR_LICENSE,MAX(N.CREATE_DATE)CREATE_DATE FROM FN_CUSTOMER_EXPAND N GROUP BY N.CAR_LICENSE) G\n" +
                "         WHERE B.CREATE_DATE = G.CREATE_DATE AND B.CAR_LICENSE = G.CAR_LICENSE) B\n" +
                "WHERE A.GET_CAR_UID =:PERSON AND TO_CHAR(A.CREATE_DATE,'YYYYMM') =:TJRQ \n" +
                "      AND A.PLATE_LICENSE = B.CAR_LICENSE AND B.JEKUN_MODEL_CODE = C.ID \n" +
                "      AND C.CAR_LABEL_ID = D.ID AND C.CAR_MODEL_ID = F.ID  ORDER BY A.CREATE_DATE DESC ";
        return toJSONArray(find(sSQL, param));
    }

    /**
     * 查询个人施工提成
     *
     * @param param
     * @return
     */
    public JSONArray queryConstructionPercentage(JSONObject param) {
        String sSQL = "SELECT A.RETAIL_ORDER_ID ORDER_NUMBER,A.PLATE_LICENSE,D.CAR_BRAND LABEL_NAME,F.NAME MODEL_NAME,A.CREATE_DATE TJRQ,A.TC_AMT TCJE,\n" +
                "      (SELECT NAME FROM MAS_PRODUCT B WHERE A.PRODUCT_ID = B.ID)PRODUCT_NAME," +
                "      (SELECT NAME FROM HR_EMPLOYEE WHERE ORIGIN = A.WORKER) SALESMAN_NAME " +
                "FROM TC_RYSGTC A ,MAS_CAR_CONFIGURATION C,MAS_CAR_LABEL D,MAS_CAR_MODEL F,\n" +
                "       ( SELECT B.ID,B.JEKUN_MODEL_CODE,B.CAR_LICENSE FROM FN_CUSTOMER_EXPAND B,\n" +
                "          (SELECT N.CAR_LICENSE,MAX(N.CREATE_DATE)CREATE_DATE FROM FN_CUSTOMER_EXPAND N GROUP BY N.CAR_LICENSE) G\n" +
                "         WHERE B.CREATE_DATE = G.CREATE_DATE AND B.CAR_LICENSE = G.CAR_LICENSE) B\n" +
                " WHERE A.WORKER =:PERSON  AND TRUNC(A.CREATE_DATE) = TRUNC(:REQUEST_DATE) \n" +
                "      AND A.PLATE_LICENSE = B.CAR_LICENSE AND B.JEKUN_MODEL_CODE = C.ID AND C.CAR_LABEL_ID = D.ID \n" +
                "      AND C.CAR_MODEL_ID = F.ID   ORDER BY A.CREATE_DATE DESC\n";
        return toJSONArray(find(sSQL, param));
    }

    /**
     * 查询个人施工提成
     *
     * @param param
     * @return
     */
    public JSONArray queryConstructionPercentageForMonth(JSONObject param) {
        String sSQL = "SELECT A.RETAIL_ORDER_ID ORDER_NUMBER, A.PLATE_LICENSE,   " +
                "       TRUNC(A.CREATE_DATE) TJRQ, A.TC_AMT TCJE,   " +
                "       (SELECT NAME FROM HR_EMPLOYEE WHERE ORIGIN = A.WORKER) SALESMAN_NAME,   " +
                "       (SELECT NAME FROM MAS_PRODUCT WHERE ID = A.PRODUCT_ID) PRODUCT_NAME,   " +
                "       D.CAR_BRAND LABEL_NAME, F.NAME MODEL_NAME   " +
                "  FROM TC_RYSGTC A, RETAIL_ORDER R, FN_CUSTOMER_EXPAND FN,   " +
                "       MAS_CAR_CONFIGURATION C, MAS_CAR_LABEL D, MAS_CAR_MODEL F   " +
                " WHERE A.RETAIL_ORDER_ID = R.ID   " +
                "   AND R.CUSTOMER_EXPAND_ID = FN.ID   " +
                "   AND FN.JEKUN_MODEL_CODE = C.ID   " +
                "   AND C.CAR_LABEL_ID = D.ID   " +
                "   AND C.CAR_MODEL_ID = F.ID   " +
                "   AND A.WORKER = :PERSON   " +
                "   AND TO_CHAR(A.CREATE_DATE, 'YYYYMM') = :TJRQ   " +
                "   ORDER BY A.CREATE_DATE DESC";
        return toJSONArray(find(sSQL, param));
    }

    /**
     * 查询门店提成明细 日
     *
     * @param param
     * @return
     */
    public JSONArray queryDeptPercentageWithDay(JSONObject param) {
        String sSQL = "SELECT A.PERSON_ID,SUM(A.TC_TOTAL)TC_TOTAL,SUM(A.XSTC)XSTC,SUM(A.SGTC)SGTC,\n" +
                "       (SELECT NAME FROM HR_EMPLOYEE WHERE ORIGIN = A.PERSON_ID) SALESMAN_NAME \n" +
                "FROM  TC_RYTCRBB A WHERE A.DEPT_ID =:DEPT_ID AND TRUNC(A.TJRQ) = TRUNC(:REQUEST_DATE)\n" +
                "GROUP BY A.PERSON_ID";
        return toJSONArray(find(sSQL, param));
    }

    /**
     * 查询门店提成明细 月
     *
     * @param param
     * @return
     */
    public JSONArray queryDeptPercentageWithMonth(JSONObject param) {
        String sSQL = "SELECT A.PERSON_ID,SUM(A.TC_TOTAL)TC_TOTAL,SUM(A.XSTC)XSTC,SUM(A.SGTC)SGTC,\n" +
                "       (SELECT NAME FROM HR_EMPLOYEE WHERE ORIGIN = A.PERSON_ID) SALESMAN_NAME \n" +
                "FROM  TC_RYTCRBB A WHERE A.DEPT_ID =:DEPT_ID AND TO_CHAR(A.TJRQ,'YYYYMM') =:TJRQ " +
                "GROUP BY A.PERSON_ID";
        return toJSONArray(find(sSQL, param));
    }

    //个人营业产值月统计
    public JSONArray queryRYYYCZForMonth(JSONObject param) {
        String sSQL = "  SELECT TO_CHAR( A.TJRQ,'YYYY-MM') TJRQ,\n" +
                "                PARTY_ROLE_ID,\n" +
                "                PARTY_ROLE_NAME,\n" +
                "                SUM (A.NKXS + A.DIYNKXS + A.XCXS + A.MRXS + A.BYXS + A.LTXS + A.WXXS + A.BPXS +\n" +
                "                A.GZXS + A.JYXS + A.JPXS + A.SHYPXS + A.JSJCXS + A.XXFWXS + A.DSJE +\n" +
                "                + A.NET_PREMIUM + A.USED_CAR_PRICE + A.SCZ + A.SDJ) YYCZ,\n" +
                "                SUM(A.NKXS + A.DIYNKXS) NKXS,\n" +
                "                SUM (  A.XCXS + A.MRXS + A.BYXS + A.LTXS + A.WXXS + A.BPXS +\n" +
                "                A.GZXS + A.JYXS + A.JPXS + A.SHYPXS + A.JSJCXS + A.XXFWXS ) FWYJ,\n" +
                "                SUM(DSJE) DSJE,\n" +
                "                SUM(NET_PREMIUM) NET_PREMIUM,\n" +
                "                SUM(USED_CAR_FINAL_PRICE) USED_CAR_FINAL_PRICE\n" +

                "              FROM TC_EMP_BUS_OUTPUT_VALUE A\n" +
                "         WHERE 1 = 1\n" +
                "         and A.PARTY_ROLE_ID = :PERSON AND TO_CHAR(A.TJRQ,'YYYYMM') = :TJRQ\n" +
                "         GROUP BY TO_CHAR(A.TJRQ,'YYYY-MM'),PARTY_ROLE_ID,PARTY_ROLE_NAME\n" +
                "         ORDER BY TO_CHAR(A.TJRQ,'YYYY-MM')  ";
        return toJSONArray(find(sSQL, param));
    }

    //个人营业产值月统计明细
    public JSONArray queryRYYYCZItemForMonth(JSONObject param) {
        String sSQL = "  SELECT \n" +
                "                sum(A.XCXS)洗车,\n" +
                "                sum(A.MRXS)美容,\n" +
                "                sum(A.BYXS)保养,\n" +
                "                sum(A.LTXS)轮胎,\n" +
                "                sum(A.WXXS)维修,\n" +
                "                sum(A.BPXS)钣喷,\n" +
                "                sum(A.GZXS)改装,\n" +
                "                sum(A.JYXS)救援,\n" +
                "                sum(A.JPXS)精品,\n" +
                "                sum(A.SHYPXS)生活用品,\n" +
                "                sum(A.JSJCXS)技术检测,\n" +
                "                sum(A.XXFWXS)信息服务\n" +
                "              FROM TC_EMP_BUS_OUTPUT_VALUE A\n" +
                "         WHERE 1 = 1\n" +
                "         and A.PARTY_ROLE_ID = :PERSON AND TO_CHAR(A.TJRQ,'YYYYMM') = :TJRQ\n" +
                "         GROUP BY TO_CHAR(A.TJRQ,'YYYY-MM'),PARTY_ROLE_ID,PARTY_ROLE_NAME\n" +
                "         ORDER BY TO_CHAR(A.TJRQ,'YYYY-MM')  ";
        return toJSONArray(find(sSQL, param));
    }

    //个人施工产值月统计
    public JSONArray queryRYSGCZForMonth(JSONObject param) {
        String sSQL = "   SELECT TO_CHAR(A.TJRQ, 'YYYY-MM') TJRQ, EMPLOYEE_ID, EMPLOYEE_NAME,\n" +
                "            ROUND(SUM(WASH_OUTPUTVALUE + COSMETOLOGY_OUTPUTVALUE + MAINTENANCE_OUTPUTVALUE\n" +
                "            + TYRE_OUTPUTVALUE + REPAIR_OUTPUTVALUE + METALPLATE_OUTPUTVALUE +RECONFIGURATION_OUTPUTVALUE\n" +
                "            +RESCUE_OUTPUTVALUE +DELICATE_OUTPUTVALUE +DAILY_ARTICLE_OUTPUTVALUE ), 2) SGCZ," +
                "            ROUND(SUM(WASH_OUTPUTVALUE + COSMETOLOGY_OUTPUTVALUE + MAINTENANCE_OUTPUTVALUE\n" +
                "            + TYRE_OUTPUTVALUE + REPAIR_OUTPUTVALUE + METALPLATE_OUTPUTVALUE +RECONFIGURATION_OUTPUTVALUE\n" +
                "            +RESCUE_OUTPUTVALUE +DELICATE_OUTPUTVALUE +DAILY_ARTICLE_OUTPUTVALUE ), 2) FWSG,\n" +
                "             ROUND(SUM(WASH_AMOUNT), 2) WASH_AMOUNT,\n" +
                "             ROUND(SUM(DELIVER_AMOUNT), 2) DELIVER_AMOUNT,\n" +
                "              ROUND(SUM(PAINT_DEAL + BOTTOM_GRINDING + SPRAY_PAINT + REFINEMENT), 2) SPRAY_PAINT_TOTAL\n" +
                "      FROM RPT_WORK_OUTPUT_VALUE A\n" +
                "     WHERE 1 = 1\n" +
                "      and A.EMPLOYEE_ID = :PERSON AND TO_CHAR(A.TJRQ,'YYYYMM') = :TJRQ\n" +
                "     GROUP BY TO_CHAR(A.TJRQ, 'YYYY-MM'), EMPLOYEE_ID, EMPLOYEE_NAME ";
        return toJSONArray(find(sSQL, param));
    }

    //个人施工产值月统计明细
    public JSONArray queryRYSGCZItemForMonth(JSONObject param) {
        String sSQL = "   SELECT " +
                "           ROUND(SUM(WASH_OUTPUTVALUE), 2) 洗车,\n" +
                "           ROUND(SUM(COSMETOLOGY_OUTPUTVALUE), 2) 美容,\n" +
                "           ROUND(SUM(MAINTENANCE_OUTPUTVALUE), 2) 保养,\n" +
                "           ROUND(SUM(TYRE_OUTPUTVALUE), 2) 轮胎,\n" +
                "           ROUND(SUM(REPAIR_OUTPUTVALUE), 2) 维修,\n" +
                "           ROUND(SUM(METALPLATE_OUTPUTVALUE), 2) 钣金,\n" +
                "           ROUND(SUM(RECONFIGURATION_OUTPUTVALUE), 2) 改装,\n" +
                "           ROUND(SUM(RESCUE_OUTPUTVALUE), 2) 救援,\n" +
                "           ROUND(SUM(DELICATE_OUTPUTVALUE), 2) 精品,\n" +
                "           ROUND(SUM(DAILY_ARTICLE_OUTPUTVALUE), 2) 生活用品\n" +
                "      FROM RPT_WORK_OUTPUT_VALUE A\n" +
                "     WHERE 1 = 1\n" +
                "      and A.EMPLOYEE_ID = :PERSON AND TO_CHAR(A.TJRQ,'YYYYMM') = :TJRQ\n" +
                "     GROUP BY TO_CHAR(A.TJRQ, 'YYYY-MM'), EMPLOYEE_ID, EMPLOYEE_NAME ";
        return toJSONArray(find(sSQL, param));
    }


    //门店个人营业产值月统计
    public JSONArray queryDeptRYCZForMonth(JSONObject param) {
        String sSQL = "  SELECT TO_CHAR(A.TJRQ, 'YYYY-MM') TJRQ,\n" +
                "       dept_id,\n" +
                "       dept_NAME,\n" +
                "       SUM(A.NKXS + A.DIYNKXS + A.XCXS + A.MRXS + A.BYXS + A.LTXS + A.WXXS +\n" +
                "           A.BPXS + A.GZXS + A.JYXS + A.JPXS + A.SHYPXS + A.JSJCXS +\n" +
                "           A.XXFWXS + A.DSJE + +A.NET_PREMIUM + A.USED_CAR_PRICE + A.SCZ +\n" +
                "           A.SDJ) YYCZ,\n" +
                "       SUM(A.NKXS + A.DIYNKXS) NKXS,\n" +
                "       SUM(A.XCXS + A.MRXS + A.BYXS + A.LTXS + A.WXXS + A.BPXS + A.GZXS +\n" +
                "           A.JYXS + A.JPXS + A.SHYPXS + A.JSJCXS + A.XXFWXS) FWYJ,\n" +
                "       SUM(DSJE) DSJE,\n" +
                "       SUM(NET_PREMIUM) NET_PREMIUM,\n" +
                "       SUM(USED_CAR_FINAL_PRICE) USED_CAR_FINAL_PRICE\n" +
                "  FROM TC_EMP_BUS_OUTPUT_VALUE A\n" +
                " WHERE 1 = 1\n" +
                "   and A.dept_id = :DEPT_ID\n" +
                "   AND TO_CHAR(A.TJRQ, 'YYYYMM') = :TJRQ\n" +
                " GROUP BY TO_CHAR(A.TJRQ, 'YYYY-MM'), dept_id, dept_name\n" +
                " ORDER BY TO_CHAR(A.TJRQ, 'YYYY-MM')\n  ";
        return toJSONArray(find(sSQL, param));
    }

    //门店个人营业产值月统计明细
    public JSONArray queryDeptRYYYCZItemForMonth(JSONObject param) {
        String sSQL = "  SELECT " +
                "       PARTY_ROLE_NAME,\n" +
                "       SUM(A.NKXS + A.DIYNKXS + A.XCXS + A.MRXS + A.BYXS + A.LTXS + A.WXXS +\n" +
                "           A.BPXS + A.GZXS + A.JYXS + A.JPXS + A.SHYPXS + A.JSJCXS +\n" +
                "           A.XXFWXS + A.DSJE + +A.NET_PREMIUM + A.USED_CAR_PRICE + A.SCZ +\n" +
                "           A.SDJ) YYCZ,\n" +
                "       SUM(A.NKXS + A.DIYNKXS) 年卡,\n" +
                "       SUM(A.XCXS + A.MRXS + A.BYXS + A.LTXS + A.WXXS + A.BPXS + A.GZXS +\n" +
                "           A.JYXS + A.JPXS + A.SHYPXS + A.JSJCXS + A.XXFWXS) 服务,\n" +
                "       SUM(DSJE) 定损,\n" +
                "       SUM(NET_PREMIUM) 车险,\n" +
                "       SUM(USED_CAR_FINAL_PRICE) 二手车\n" +
                "  FROM TC_EMP_BUS_OUTPUT_VALUE A\n" +
                " WHERE 1 = 1\n" +
                "   and A.dept_id = :DEPT_ID\n" +
                "   AND TO_CHAR(A.TJRQ, 'YYYYMM') = :TJRQ\n" +
                " GROUP BY TO_CHAR(A.TJRQ, 'YYYY-MM'), PARTY_ROLE_ID, PARTY_ROLE_NAME\n" +
                " ORDER BY TO_CHAR(A.TJRQ, 'YYYY-MM')\n  ";
        return toJSONArray(find(sSQL, param));
    }

    //个人施工产值月统计
    public JSONArray queryDeptRYSGCZForMonth(JSONObject param) {
        String sSQL = "   SELECT TO_CHAR(A.TJRQ, 'YYYY-MM') TJRQ, DEPT_ID, DEPT_NAME,\n" +
                "            ROUND(SUM(WASH_OUTPUTVALUE + COSMETOLOGY_OUTPUTVALUE + MAINTENANCE_OUTPUTVALUE\n" +
                "            + TYRE_OUTPUTVALUE + REPAIR_OUTPUTVALUE + METALPLATE_OUTPUTVALUE +RECONFIGURATION_OUTPUTVALUE\n" +
                "            +RESCUE_OUTPUTVALUE +DELICATE_OUTPUTVALUE +DAILY_ARTICLE_OUTPUTVALUE ), 2) SGCZ," +
                "            ROUND(SUM(WASH_OUTPUTVALUE + COSMETOLOGY_OUTPUTVALUE + MAINTENANCE_OUTPUTVALUE\n" +
                "            + TYRE_OUTPUTVALUE + REPAIR_OUTPUTVALUE + METALPLATE_OUTPUTVALUE +RECONFIGURATION_OUTPUTVALUE\n" +
                "            +RESCUE_OUTPUTVALUE +DELICATE_OUTPUTVALUE +DAILY_ARTICLE_OUTPUTVALUE ), 2) FWSG,\n" +
                "             ROUND(SUM(WASH_AMOUNT), 2) WASH_AMOUNT,\n" +
                "             ROUND(SUM(DELIVER_AMOUNT), 2) DELIVER_AMOUNT,\n" +
                "              ROUND(SUM(PAINT_DEAL + BOTTOM_GRINDING + SPRAY_PAINT + REFINEMENT), 2) SPRAY_PAINT_TOTAL\n" +
                "      FROM RPT_WORK_OUTPUT_VALUE A\n" +
                "     WHERE 1 = 1\n" +
                "      and A.DEPT_ID = :DEPT_ID AND TO_CHAR(A.TJRQ,'YYYYMM') = :TJRQ\n" +
                "     GROUP BY TO_CHAR(A.TJRQ, 'YYYY-MM'), DEPT_ID, DEPT_NAME ";
        return toJSONArray(find(sSQL, param));
    }

    //个人施工产值月统计明细
    public JSONArray queryDeptRYSGCZItemForMonth(JSONObject param) {
        String sSQL = "   SELECT EMPLOYEE_NAME,\n" +
                "            ROUND(SUM(WASH_OUTPUTVALUE + COSMETOLOGY_OUTPUTVALUE + MAINTENANCE_OUTPUTVALUE\n" +
                "            + TYRE_OUTPUTVALUE + REPAIR_OUTPUTVALUE + METALPLATE_OUTPUTVALUE +RECONFIGURATION_OUTPUTVALUE\n" +
                "            +RESCUE_OUTPUTVALUE +DELICATE_OUTPUTVALUE +DAILY_ARTICLE_OUTPUTVALUE ), 2) SGCZ," +
                "            ROUND(SUM(WASH_OUTPUTVALUE + COSMETOLOGY_OUTPUTVALUE + MAINTENANCE_OUTPUTVALUE\n" +
                "            + TYRE_OUTPUTVALUE + REPAIR_OUTPUTVALUE + METALPLATE_OUTPUTVALUE +RECONFIGURATION_OUTPUTVALUE\n" +
                "            +RESCUE_OUTPUTVALUE +DELICATE_OUTPUTVALUE +DAILY_ARTICLE_OUTPUTVALUE ), 2) 服务施工,\n" +
                "             ROUND(SUM(WASH_AMOUNT), 2) 洗车台次,\n" +
                "             ROUND(SUM(DELIVER_AMOUNT), 2) 接送车台次,\n" +
                "              ROUND(SUM(PAINT_DEAL + BOTTOM_GRINDING + SPRAY_PAINT + REFINEMENT), 2) 喷漆副数\n" +
                "      FROM RPT_WORK_OUTPUT_VALUE A\n" +
                "     WHERE 1 = 1\n" +
                "      and A.DEPT_ID = :DEPT_ID AND TO_CHAR(A.TJRQ,'YYYYMM') = :TJRQ\n" +
                "     GROUP BY TO_CHAR(A.TJRQ, 'YYYY-MM'), EMPLOYEE_ID, EMPLOYEE_NAME ";

        return toJSONArray(find(sSQL, param));
    }


    public JSONArray queryXSETCById(JSONObject param) {
        String sSQL = "  select a.main_mobile,\n" +
                "           a.sub_mobile,\n" +
                "           a.DEPT_ID,\n" +
                "           a.main_salesman,\n" +
                "           sum(a.main_salesman_tcje) main_salesman_tcje,\n" +
                "           a.sub_salesman,\n" +
                "           sum(a.sub_salesman_tcje) sub_salesman_tcje\n" +
                "      from (SELECT t.main_salesman,\n" +
                "                   t.DEPT_ID,\n" +
                "                   (SELECT t.mobile_number\n" +
                "                      FROM hr_employee t\n" +
                "                     WHERE t.party_id =\n" +
                "                           (SELECT t.party_id\n" +
                "                              FROM mas_party_role t\n" +
                "                             WHERE t.id = t.main_salesman)) main_mobile,\n" +
                "                   t.main_salesman_tcje,\n" +
                "                   t.sub_salesman,\n" +
                "                   (SELECT t.mobile_number\n" +
                "                      FROM hr_employee t\n" +
                "                     WHERE t.party_id =\n" +
                "                           (SELECT t.party_id\n" +
                "                              FROM mas_party_role t\n" +
                "                             WHERE t.id = t.sub_salesman)) sub_mobile,\n" +
                "                   t.sub_salesman_tcje\n" +
                "              FROM tc_xsetc t\n" +
                "             WHERE t.retail_order_id = :RETAIL_ORDER_ID\n" +
                "            union all\n" +
                "            SELECT t.SALESMAN main_salesman,\n" +
                "                   t.DEPT_ID,\n" +
                "                   (SELECT t.mobile_number\n" +
                "                      FROM hr_employee t\n" +
                "                     WHERE t.party_id =\n" +
                "                           (SELECT t.party_id\n" +
                "                              FROM mas_party_role t\n" +
                "                             WHERE t.id = t.SALESMAN)) main_mobile,\n" +
                "                   t.main_salesman_tcje,\n" +
                "                   t.SUB_SALESMAN sub_salesman,\n" +
                "                   (SELECT t.mobile_number\n" +
                "                      FROM hr_employee t\n" +
                "                     WHERE t.party_id =\n" +
                "                           (SELECT t.party_id\n" +
                "                              FROM mas_party_role t\n" +
                "                             WHERE t.id = t.SUB_SALESMAN)) sub_mobile,\n" +
                "                   t.sub_salesman_tcje\n" +
                "              FROM TC_NKTC t\n" +
                "             WHERE t.RETAIL_ORDER_ID = :RETAIL_ORDER_ID\n" +
                "            union all\n" +
                "            SELECT t.WORKER main_salesman,\n" +
                "                   t.DEPT_ID,\n" +
                "                   (SELECT t.mobile_number\n" +
                "                      FROM hr_employee t\n" +
                "                     WHERE t.party_id = (SELECT t.party_id\n" +
                "                                           FROM mas_party_role t\n" +
                "                                          WHERE t.id = t.WORKER)) main_mobile,\n" +
                "                   t.TC_AMT main_salesman_tcje,\n" +
                "                   null sub_salesman,\n" +
                "                   null sub_mobile,\n" +
                "                   null sub_salesman_tcje\n" +
                "              FROM tc_rysgtc t\n" +
                "             WHERE t.RETAIL_ORDER_ID = :RETAIL_ORDER_ID" +
                "             union all\n" +
                "             SELECT T.bill_man main_salesman,\n" +
                "                    T.DEPT_ID,\n" +
                "                    (SELECT t.mobile_number\n" +
                "                       FROM hr_employee t\n" +
                "                     WHERE t.party_id = (SELECT t.party_id\n" +
                "                                                 FROM mas_party_role t\n" +
                "                                                 WHERE t.id = T.bill_man)) main_mobile,\n" +
                "                      T.tc_amt main_salesman_tcje,\n" +
                "                      null sub_salesman,\n" +
                "                      null sub_mobile,\n" +
                "                      null sub_salesman_tcje  \n" +
                "                      FROM tc_kdtc T" +
                "                     WHERE t.RETAIL_ORDER_ID = :RETAIL_ORDER_ID) a\n" +
                "     group by a.main_mobile,\n" +
                "              a.sub_mobile,\n" +
                "              a.DEPT_ID,\n" +
                "              a.main_salesman,\n" +
                "              a.sub_salesman";
        return toJSONArray(find(sSQL, param));
    }


    public JSONArray queryKDTCById(JSONObject param) {
        String sSQL = "   \n" +
                " SELECT a.main_mobile,\n" +
                "        a.sub_mobile,\n" +
                "        a.DEPT_ID,\n" +
                "        a.main_salesman,\n" +
                "        sum(a.main_salesman_tcje) main_salesman_tcje,\n" +
                "        a.sub_salesman,\n" +
                "        sum(a.sub_salesman_tcje) sub_salesman_tcje\n" +
                "   FROM (SELECT t.BILL_MAN main_salesman,\n" +
                "                t.DEPT_ID,\n" +
                "                (SELECT t.mobile_number\n" +
                "                   FROM hr_employee t\n" +
                "                  WHERE t.party_id = (SELECT t.party_id\n" +
                "                                        FROM mas_party_role t\n" +
                "                                       WHERE t.id = t.BILL_MAN)) main_mobile,\n" +
                "                T.TC_AMT main_salesman_tcje,\n" +
                "                NULL sub_salesman,\n" +
                "                NULL sub_mobile,\n" +
                "                NULL sub_salesman_tcje\n" +
                "         \n" +
                "           FROM TC_KDTC t\n" +
                "          WHERE T.FN_INNER_PICK_DEL_UP_CAR_ID = :FN_INNER_PICK_DEL_UP_CAR_ID\n" +
                "         UNION ALL\n" +
                "         SELECT t.JSCR main_salesman,\n" +
                "                t.DISPATCHER_DEPT_ID DEPT_ID,\n" +
                "                (SELECT t.mobile_number\n" +
                "                   FROM hr_employee t\n" +
                "                  WHERE t.party_id = (SELECT t.party_id\n" +
                "                                        FROM mas_party_role t\n" +
                "                                       WHERE t.id = t.JSCR)) main_mobile,\n" +
                "                T.TC_AMT main_salesman_tcje,\n" +
                "                NULL sub_salesman,\n" +
                "                NULL sub_mobile,\n" +
                "                NULL sub_salesman_tcje\n" +
                "         \n" +
                "           FROM TC_JSCTC t\n" +
                "          WHERE T.FN_INNER_PICK_DEL_UP_CAR_ID = :FN_INNER_PICK_DEL_UP_CAR_ID) A\n" +
                "  group by a.main_mobile,\n" +
                "           a.sub_mobile,\n" +
                "           a.DEPT_ID,\n" +
                "           a.main_salesman,\n" +
                "           a.sub_salesman\n";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray queryCXTCById(JSONObject param) {
        String sSQL = "   SELECT\n" +
                "  (SELECT t.mobile_number\n" +
                "     FROM hr_employee t\n" +
                "    WHERE t.party_id =\n" +
                "          (SELECT t.party_id FROM mas_party_role t WHERE t.id = t.SALESMAN_ID)) main_mobile,\n" +
                "  NULL sub_mobile,\n" +
                "  t.DEPT_ID,\n" +
                "  T.FN_PROFIT main_salesman_tcje,\n" +
                "  NULL          sub_salesman_tcje,\n" +
                "  t.SALESMAN_ID main_salesman,\n" +
                "  NULL          sub_salesman\n" +
                "   FROM TC_CXTC t\n" +
                "  WHERE T.ID = :ID";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray queryDSXSETCById(JSONObject param) {
        String sSQL = " SELECT\n" +
                "            (SELECT t.mobile_number\n" +
                "                    FROM hr_employee t\n" +
                "                    WHERE t.party_id =\n" +
                "                    (SELECT t.party_id FROM mas_party_role t WHERE t.id = t.get_car_uid)) main_mobile,\n" +
                "    NULL sub_mobile,\n" +
                "    t.DEPT_ID,\n" +
                "    T.tcje main_salesman_tcje,\n" +
                "    NULL          sub_salesman_tcje,\n" +
                "    t.get_car_uid main_salesman,\n" +
                "    NULL          sub_salesman\n" +
                "    FROM TC_DSXSETC t\n" +
                "    WHERE T.source = :ID";
        return toJSONArray(find(sSQL, param));
    }

    //查看人员预估提成明细
    public JSONArray getPersonalEstimatedCommissionDetail(JSONObject param) {
        String sql = "SELECT A.ID,C.CAR_LICENSE,A.CREATE_DATE,A.BALANCE_AMOUNT,\n" +
                "  (CASE WHEN B.SUP_SALER_ID IS NULL THEN ESTIMATED_COMMISSION ELSE \n" +
                "  (B.ESTIMATED_COMMISSION/2) END )COMMISSION,DECODE(A.STATE,'confirm','确认','qualityTest','质检')STATE,\n" +
                "  (SELECT E.CAR_BRAND FROM MAS_CAR_CONFIGURATION D,MAS_CAR_LABEL E WHERE D.CAR_LABEL_ID = E.ID AND C.JEKUN_MODEL_CODE = D.ID)CAR_BRAND,\n" +
                "  (SELECT E.NAME FROM MAS_CAR_CONFIGURATION D,MAS_CAR_MODEL E WHERE D.CAR_MODEL_ID = E.ID AND C.JEKUN_MODEL_CODE = D.ID)CAR_MODEL,\n" +
                "  (SELECT NAME FROM MAS_PRODUCT WHERE B.PRODUCT_ID = ID)PRODUCT,\n" +
                "  (SELECT NAME FROM HR_EMPLOYEE WHERE ORIGIN = B.MAIN_SALER_ID) MAIN_SALESMAN,\n" +
                "  (SELECT NAME FROM HR_EMPLOYEE WHERE ORIGIN = B.SUP_SALER_ID) SUP_SALESMAN\n" +
                "FROM SER_SERVICE_PLAN A,SER_SERVICE_PLAN_ITEM B,FN_CUSTOMER_EXPAND C \n" +
                "WHERE A.ID = B.SERVICE_PLAN_ID AND A.FN_CUSTOMER_EXPAND_ID = C.ID\n" +
                "AND A.STATE IN ('confirm','qualityTest') AND (B.SUP_SALER_ID =:PERSON OR B.MAIN_SALER_ID =:PERSON )";
        return toJSONArray(find(sql, param));
    }


    //查看人员预估提成明细
    public JSONArray getPersonalEstimatedCommission(JSONObject param) {
        String sql = "SELECT SUM(CASE WHEN B.SUP_SALER_ID IS NULL THEN ESTIMATED_COMMISSION ELSE \n" +
                "            (B.ESTIMATED_COMMISSION/2) END )COMMISSION\n" +
                "FROM SER_SERVICE_PLAN A,SER_SERVICE_PLAN_ITEM B \n" +
                "WHERE A.ID = B.SERVICE_PLAN_ID " +
                "AND A.STATE IN ('confirm','qualityTest') AND (B.SUP_SALER_ID =:PERSON OR B.MAIN_SALER_ID =:PERSON )";
        return toJSONArray(find(sql, param));
    }


    //查看人员预估产值排名
    public JSONArray getPersonCZRank(JSONObject param) {
        String sql = " SELECT A.PARTY_ROLE_ID,\n" +
                "        A.PARTY_ROLE_NAME,\n" +
                "        sum(A.YYCZ+A.SGCZ) AMOUNT," +
                "        'CZ' TYPE\n" +
                "   FROM (SELECT T.COMPANY_ID,\n" +
                "                T.COMPANY_NAME,\n" +
                "                T.PARTY_ROLE_ID,\n" +
                "                T.PARTY_ROLE_NAME,\n" +
                "                T.DEPT_ID,\n" +
                "                T.DEPT_NAME,\n" +
                "                TO_CHAR(T.TJRQ, 'YYYY-MM-DD') TJRQ_CHAR,\n" +
                "                TJRQ,\n" +
                "                SUM(T.NKXS + DIYNKXS + PTXCXS + XCXS + MRXS + BYXS + LTXS + WXXS + BPXS + GZXS + JYXS + JPXS +\n" +
                "                    SHYPXS + JSJCXS + XXFWXS + JCJSF + FN_COMMISSION +\n" +
                "                    USED_CAR_PRICE + SCZ + SDJ + NSXS) AS YYCZ,\n" +
                "                SUM(T.NKXSTC + DIYNKXSTC + XCXSTC + MRXSTC + BYXSTC + LTXSTC +\n" +
                "                    WXXSTC + BPXSTC + GZXSTC + JYXSTC + JPXSTC + SHYPXSTC +\n" +
                "                    JSJCXSTC + XXFWXSTC + JCJSFTC + NET_PREMIUM_TC +\n" +
                "                    USED_CAR_PRICE_TC + DSGZTC +NSXSTC +KDTC) AS YYCZTC,\n" +
                "                0 SGCZ,\n" +
                "                0 WASH_AMOUNT,\n" +
                "                0 SPRAY_PAINT,\n" +
                "                0 SGTC\n" +
                "           FROM TC_EMP_BUS_OUTPUT_VALUE T\n" +
                "          GROUP BY TO_CHAR(T.TJRQ, 'YYYY-MM-DD'),\n" +
                "                   TJRQ,\n" +
                "                   COMPANY_ID,\n" +
                "                   COMPANY_NAME,\n" +
                "                   DEPT_ID,\n" +
                "                   DEPT_NAME,\n" +
                "                   PARTY_ROLE_ID,\n" +
                "                   PARTY_ROLE_NAME\n" +
                "         UNION ALL\n" +
                "         SELECT T.COMPANY_ID,\n" +
                "                T.COMPANY_NAME,\n" +
                "                T.EMPLOYEE_ID PARTY_ROLE_ID,\n" +
                "                T.EMPLOYEE_NAME PARTY_ROLE_NAME,\n" +
                "                T.DEPT_ID,\n" +
                "                T.DEPT_NAME,\n" +
                "                TO_CHAR(T.TJRQ, 'YYYY-MM-DD') TJRQ_CHAR,\n" +
                "                TJRQ,\n" +
                "                0 YYCZ,\n" +
                "                0 YYCZTC,\n" +
                "                SUM(ROUND(WORK_OUTPUTVALUE_ALL, 2)) AS SGCZ,\n" +
                "                SUM(WASH_AMOUNT) WASH_AMOUNT,\n" +
                "                SUM(SPRAY_PAINT) SPRAY_PAINT,\n" +
                "                SUM(ROUND(WORK_COMMISION_ALL, 2)) AS SGTC\n" +
                "           FROM RPT_WORK_OUTPUT_VALUE T\n" +
                "          \n" +
                "          GROUP BY TO_CHAR(T.TJRQ, 'YYYY-MM-DD'),\n" +
                "                   TJRQ,\n" +
                "                   COMPANY_ID,\n" +
                "                   COMPANY_NAME,\n" +
                "                   DEPT_ID,\n" +
                "                   DEPT_NAME,\n" +
                "                   EMPLOYEE_ID,\n" +
                "                   EMPLOYEE_NAME) A\n" +
                "  WHERE 1 = 1\n" +
                "   AND DEPT_ID = :DEPT_ID\n" +
                "   AND TJRQ = to_date(:TJRQ,'YYYY-MM-DD')\n" +
                "  GROUP BY TJRQ_CHAR,\n" +
                "           TJRQ,\n" +
                "           COMPANY_ID,\n" +
                "           COMPANY_NAME,\n" +
                "           DEPT_ID,\n" +
                "           DEPT_NAME,\n" +
                "           PARTY_ROLE_ID,\n" +
                "           PARTY_ROLE_NAME\n" +
                "  ORDER BY sum(A.YYCZ+A.SGCZ) desc";
        return toJSONArray(find(sql, param));
    }

    //查看人员预估提成排名
    public JSONArray getPersonTCRank(JSONObject param) {
        String sql = " SELECT A.PARTY_ROLE_ID,\n" +
                "        A.PARTY_ROLE_NAME,\n" +
                "        sum(A.YYCZTC+A.SGTC) AMOUNT," +
                "        'TC' TYPE\n" +
                "   FROM (SELECT T.COMPANY_ID,\n" +
                "                T.COMPANY_NAME,\n" +
                "                T.PARTY_ROLE_ID,\n" +
                "                T.PARTY_ROLE_NAME,\n" +
                "                T.DEPT_ID,\n" +
                "                T.DEPT_NAME,\n" +
                "                TO_CHAR(T.TJRQ, 'YYYY-MM-DD') TJRQ_CHAR,\n" +
                "                TJRQ,\n" +
                "                SUM(T.NKXS + DIYNKXS + PTXCXS + XCXS + MRXS + BYXS + LTXS + WXXS + BPXS + GZXS + JYXS + JPXS +\n" +
                "                    SHYPXS + JSJCXS + XXFWXS + JCJSF + FN_COMMISSION +\n" +
                "                    USED_CAR_PRICE + SCZ + SDJ + NSXS) AS YYCZ,\n" +
                "                SUM(T.NKXSTC + DIYNKXSTC + XCXSTC + MRXSTC + BYXSTC + LTXSTC +\n" +
                "                    WXXSTC + BPXSTC + GZXSTC + JYXSTC + JPXSTC + SHYPXSTC +\n" +
                "                    JSJCXSTC + XXFWXSTC + JCJSFTC + NET_PREMIUM_TC +\n" +
                "                    USED_CAR_PRICE_TC + DSGZTC +NSXSTC +KDTC) AS YYCZTC,\n" +
                "                0 SGCZ,\n" +
                "                0 WASH_AMOUNT,\n" +
                "                0 SPRAY_PAINT,\n" +
                "                0 SGTC\n" +
                "           FROM TC_EMP_BUS_OUTPUT_VALUE T\n" +
                "          GROUP BY TO_CHAR(T.TJRQ, 'YYYY-MM-DD'),\n" +
                "                   TJRQ,\n" +
                "                   COMPANY_ID,\n" +
                "                   COMPANY_NAME,\n" +
                "                   DEPT_ID,\n" +
                "                   DEPT_NAME,\n" +
                "                   PARTY_ROLE_ID,\n" +
                "                   PARTY_ROLE_NAME\n" +
                "         UNION ALL\n" +
                "         SELECT T.COMPANY_ID,\n" +
                "                T.COMPANY_NAME,\n" +
                "                T.EMPLOYEE_ID PARTY_ROLE_ID,\n" +
                "                T.EMPLOYEE_NAME PARTY_ROLE_NAME,\n" +
                "                T.DEPT_ID,\n" +
                "                T.DEPT_NAME,\n" +
                "                TO_CHAR(T.TJRQ, 'YYYY-MM-DD') TJRQ_CHAR,\n" +
                "                TJRQ,\n" +
                "                0 YYCZ,\n" +
                "                0 YYCZTC,\n" +
                "                SUM(ROUND(WORK_OUTPUTVALUE_ALL, 2)) AS SGCZ,\n" +
                "                SUM(WASH_AMOUNT) WASH_AMOUNT,\n" +
                "                SUM(SPRAY_PAINT) SPRAY_PAINT,\n" +
                "                SUM(ROUND(WORK_COMMISION_ALL, 2)) AS SGTC\n" +
                "           FROM RPT_WORK_OUTPUT_VALUE T\n" +
                "          \n" +
                "          GROUP BY TO_CHAR(T.TJRQ, 'YYYY-MM-DD'),\n" +
                "                   TJRQ,\n" +
                "                   COMPANY_ID,\n" +
                "                   COMPANY_NAME,\n" +
                "                   DEPT_ID,\n" +
                "                   DEPT_NAME,\n" +
                "                   EMPLOYEE_ID,\n" +
                "                   EMPLOYEE_NAME) A\n" +
                "  WHERE 1 = 1\n" +
                "   AND DEPT_ID = :DEPT_ID\n" +
                "   AND TJRQ = to_date(:TJRQ,'YYYY-MM-DD')\n" +
                "  GROUP BY TJRQ_CHAR,\n" +
                "           TJRQ,\n" +
                "           COMPANY_ID,\n" +
                "           COMPANY_NAME,\n" +
                "           DEPT_ID,\n" +
                "           DEPT_NAME,\n" +
                "           PARTY_ROLE_ID,\n" +
                "           PARTY_ROLE_NAME\n" +
                "  ORDER BY sum(A.YYCZTC+A.SGTC) desc";
        return toJSONArray(find(sql, param));
    }

    //查看门店营业额排名
    public JSONArray getSaleMoneyRank(JSONObject param) {
        String sql = " SELECT t.DATE_OF_STATISTICS,t.DEPT_ID,\n" +
                "t.DEPT_NAME,t.COMPANY_ID,t.COMPANY_NAME,t.SALE_MONEY ,rownum FROM ( \n" +
                " SELECT DATE_OF_STATISTICS,\n" +
                "              DEPT_ID,\n" +
                "              (SELECT NAME FROM MAS_DEPT A WHERE A.ID = U.DEPT_ID) DEPT_NAME,\n" +
                "              COMPANY_ID,\n" +
                "              (SELECT C.NAME\n" +
                "              FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "              WHERE C.ID = D.PARTY_ID\n" +
                "              AND D.ID = U.COMPANY_ID) COMPANY_NAME, SALE_MONEY\n" +
                "        FROM RCL_DAILYSALESREPORT U\n" +
                "      WHERE 1 = 1\n" +
                "      ORDER BY SALE_MONEY DESC) t WHERE t.DATE_OF_STATISTICS = to_date(:TJRQ,'YYYY-MM-DD')";
        return toJSONArray(find(sql, param));
    }


    //app获取产值/提成统计（日）
    public JSONArray getDeptBusValueRank(JSONObject param) {
        String sql = " SELECT A.DEPT_ID, (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME,\n" +
                " SUM(A.YYCZ) YYCZ, SUM(A.YYCZTC) YYCZTC, SUM(A.SGCZ) SGCZ,\n" +
                " SUM(A.WASH_AMOUNT) WASH_AMOUNT, SUM(A.SPRAY_PAINT) SPRAY_PAINT,\n" +
                " SUM(A.SGTC) SGTC, sum(A.YYCZ + A.SGCZ) CZ, sum(A.YYCZTC + A.SGTC) TC\n" +
                " FROM (SELECT T.COMPANY_ID, T.DEPT_ID,\n" +
                " TO_CHAR(T.TJRQ, 'YYYY-MM-DD') TJRQ_CHAR, TJRQ,\n" +
                " SUM(T.NKXS + DIYNKXS + PTXCXS + XCXS + MRXS + BYXS + LTXS + WXXS + BPXS + GZXS + JYXS + JPXS +\n" +
                "         SHYPXS + JSJCXS + XXFWXS + JCJSF + FN_COMMISSION +\n" +
                "         USED_CAR_PRICE + SCZ + SDJ + NSXS) AS YYCZ,\n" +
                " SUM(T.NKXSTC + DIYNKXSTC + XCXSTC + MRXSTC + BYXSTC + LTXSTC +\n" +
                "         WXXSTC + BPXSTC + GZXSTC + JYXSTC + JPXSTC + SHYPXSTC +\n" +
                "         JSJCXSTC + XXFWXSTC + JCJSFTC + NET_PREMIUM_TC +\n" +
                "         USED_CAR_PRICE_TC + DSGZTC + NSXSTC +KDTC) AS YYCZTC, 0 SGCZ,\n" +
                " 0 WASH_AMOUNT, 0 SPRAY_PAINT, 0 SGTC\n" +
                " FROM TC_EMP_BUS_OUTPUT_VALUE T\n" +
                " GROUP BY TO_CHAR(T.TJRQ, 'YYYY-MM-DD'), TJRQ, COMPANY_ID, DEPT_ID\n" +
                " UNION ALL\n" +
                " SELECT T.COMPANY_ID, T.DEPT_ID,\n" +
                " TO_CHAR(T.TJRQ, 'YYYY-MM-DD') TJRQ_CHAR, TJRQ, 0 YYCZ,\n" +
                " 0 YYCZTC, SUM(ROUND(WORK_OUTPUTVALUE_ALL, 2)) AS SGCZ,\n" +
                " SUM(WASH_AMOUNT) WASH_AMOUNT, 0 SPRAY_PAINT,\n" +
                " SUM(ROUND(WORK_COMMISION_ALL, 2)) AS SGTC\n" +
                " FROM RPT_WORK_OUTPUT_VALUE T\n" +
                " GROUP BY TO_CHAR(T.TJRQ, 'YYYY-MM-DD'), TJRQ, COMPANY_ID, DEPT_ID\n" +
                " UNION ALL\n" +
                " SELECT T.COMPANY_ID, T.DEPT_ID,\n" +
                " TO_CHAR(T.CREATE_DATE, 'YYYY-MM-DD') TJRQ_CHAR, CREATE_DATE TJRQ, 0 YYCZ,\n" +
                " 0 YYCZTC, 0 SGCZ, 0 WASH_AMOUNT,\n" +
                " SUM(T.SALE_QTY * T.SPECIFICATIONS) SPRAY_PAINT, 0 SGTC\n" +
                " FROM TC_DJSGTC T\n" +
                " GROUP BY TO_CHAR(T.CREATE_DATE, 'YYYY-MM-DD'), T.CREATE_DATE, T.COMPANY_ID,\n" +
                " T.DEPT_ID) A\n" +
                " WHERE 1 = 1\n" +
                " AND DEPT_ID = :DEPT_ID \n" +
                " AND TJRQ = TO_DATE(:TJRQ,'YYYY-MM-DD')\n" +
                " GROUP BY DEPT_ID";
        return toJSONArray(find(sql, param));
    }

    //app获取产值/提成统计（月）
    public JSONArray getDeptBusValueMon(JSONObject param) {
        String sql = " SELECT A.DEPT_ID, (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME,\n" +
                " SUM(A.YYCZ) YYCZ, SUM(A.YYCZTC) YYCZTC, SUM(A.SGCZ) SGCZ,\n" +
                " SUM(A.WASH_AMOUNT) WASH_AMOUNT, SUM(A.SPRAY_PAINT) SPRAY_PAINT,\n" +
                " SUM(A.SGTC) SGTC, sum(A.YYCZ + A.SGCZ) CZ, sum(A.YYCZTC + A.SGTC) TC\n" +
                " FROM (SELECT T.COMPANY_ID, T.DEPT_ID,\n" +
                " TO_CHAR(T.TJRQ, 'YYYY-MM-DD') TJRQ_CHAR, TJRQ,\n" +
                " SUM(T.NKXS + DIYNKXS + PTXCXS + XCXS + MRXS + BYXS + LTXS + WXXS + BPXS + GZXS + JYXS + JPXS +\n" +
                "         SHYPXS + JSJCXS + XXFWXS + JCJSF + FN_COMMISSION +\n" +
                "         USED_CAR_PRICE + SCZ + SDJ + NSXS) AS YYCZ,\n" +
                " SUM(T.NKXSTC + DIYNKXSTC + XCXSTC + MRXSTC + BYXSTC + LTXSTC +\n" +
                "         WXXSTC + BPXSTC + GZXSTC + JYXSTC + JPXSTC + SHYPXSTC +\n" +
                "         JSJCXSTC + XXFWXSTC + JCJSFTC + NET_PREMIUM_TC +\n" +
                "         USED_CAR_PRICE_TC + DSGZTC + NSXSTC +KDTC) AS YYCZTC, 0 SGCZ,\n" +
                " 0 WASH_AMOUNT, 0 SPRAY_PAINT, 0 SGTC\n" +
                " FROM TC_EMP_BUS_OUTPUT_VALUE T\n" +
                " GROUP BY TO_CHAR(T.TJRQ, 'YYYY-MM-DD'), TJRQ, COMPANY_ID, DEPT_ID\n" +
                " UNION ALL\n" +
                " SELECT T.COMPANY_ID, T.DEPT_ID,\n" +
                " TO_CHAR(T.TJRQ, 'YYYY-MM-DD') TJRQ_CHAR, TJRQ, 0 YYCZ,\n" +
                " 0 YYCZTC, SUM(ROUND(WORK_OUTPUTVALUE_ALL, 2)) AS SGCZ,\n" +
                " SUM(WASH_AMOUNT) WASH_AMOUNT, 0 SPRAY_PAINT,\n" +
                " SUM(ROUND(WORK_COMMISION_ALL, 2)) AS SGTC\n" +
                " FROM RPT_WORK_OUTPUT_VALUE T\n" +
                " GROUP BY TO_CHAR(T.TJRQ, 'YYYY-MM-DD'), TJRQ, COMPANY_ID, DEPT_ID\n" +
                " UNION ALL\n" +
                " SELECT T.COMPANY_ID, T.DEPT_ID,\n" +
                " TO_CHAR(T.CREATE_DATE, 'YYYY-MM-DD') TJRQ_CHAR, CREATE_DATE TJRQ, 0 YYCZ,\n" +
                " 0 YYCZTC, 0 SGCZ, 0 WASH_AMOUNT,\n" +
                " SUM(T.SALE_QTY * T.SPECIFICATIONS) SPRAY_PAINT, 0 SGTC\n" +
                " FROM TC_DJSGTC T\n" +
                " GROUP BY TO_CHAR(T.CREATE_DATE, 'YYYY-MM-DD'), T.CREATE_DATE, T.COMPANY_ID,\n" +
                " T.DEPT_ID) A\n" +
                " WHERE 1 = 1\n" +
                " AND DEPT_ID = :DEPT_ID \n" +
                " AND TJRQ >= TO_DATE(:TJRQ,'YYYY-MM-DD')\n" +
                " AND TJRQ <= TO_DATE(:TJRQEND,'YYYY-MM-DD')\n" +
                " GROUP BY DEPT_ID";
        return toJSONArray(find(sql, param));
    }


    //app获取人员产值排名（日）
    public JSONArray getEmpValueRank(JSONObject param) {
        String sql = "SELECT T.PARTY_ROLE_ID,T.PARTY_ROLE_NAME,T.DEPT_ID,T.DEPT_NAME,\n" +
                "T.CZ,T.YYCZ,T.SGCZ,T.WASH_AMOUNT,SPRAY_PAINT,ROWNUM," +
                "SUM(T.CZ) OVER (PARTITION BY T.DEPT_ID ) SUMTOTAL FROM  \n" +
                "(SELECT \n" +
                "       A.PARTY_ROLE_ID,\n" +
                "       A.PARTY_ROLE_NAME,\n" +
                "       A.DEPT_ID,\n" +
                "       A.DEPT_NAME,\n" +
                "       SUM(A.YYCZ + A.SGCZ) CZ,\n" +
                "       SUM(A.YYCZ) YYCZ,\n" +
                "       SUM(A.SGCZ) SGCZ,\n" +
                "       SUM(A.WASH_AMOUNT) WASH_AMOUNT,\n" +
                "       SUM(A.SPRAY_PAINT) SPRAY_PAINT\n" +
                "  FROM (SELECT T.COMPANY_ID,\n" +
                "               T.COMPANY_NAME,\n" +
                "               T.PARTY_ROLE_ID,\n" +
                "               T.PARTY_ROLE_NAME,\n" +
                "               T.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT WHERE ID = T.DEPT_ID) DEPT_NAME,\n" +
                "               TO_CHAR(T.TJRQ, 'YYYY-MM-DD') TJRQ_CHAR,\n" +
                "               TJRQ,\n" +
                "                SUM(T.NKXS + DIYNKXS + PTXCXS + XCXS + MRXS + BYXS + LTXS + WXXS + BPXS + GZXS + JYXS + JPXS +\n" +
                "                    SHYPXS + JSJCXS + XXFWXS + JCJSF + FN_COMMISSION +\n" +
                "                    USED_CAR_PRICE + SCZ + SDJ + NSXS) AS YYCZ,\n" +
                "                SUM(T.NKXSTC + DIYNKXSTC + XCXSTC + MRXSTC + BYXSTC + LTXSTC +\n" +
                "                    WXXSTC + BPXSTC + GZXSTC + JYXSTC + JPXSTC + SHYPXSTC +\n" +
                "                    JSJCXSTC + XXFWXSTC + JCJSFTC + NET_PREMIUM_TC +\n" +
                "                    USED_CAR_PRICE_TC + DSGZTC +NSXSTC +KDTC) AS YYCZTC,\n" +
                "               0 SGCZ,\n" +
                "               0 WASH_AMOUNT,\n" +
                "               0 SPRAY_PAINT,\n" +
                "               0 SGTC\n" +
                "          FROM TC_EMP_BUS_OUTPUT_VALUE T\n" +
                "         GROUP BY TO_CHAR(T.TJRQ, 'YYYY-MM-DD'),\n" +
                "                  TJRQ,\n" +
                "                  COMPANY_ID,\n" +
                "                  COMPANY_NAME,\n" +
                "                  DEPT_ID,\n" +
                "                  DEPT_NAME,\n" +
                "                  PARTY_ROLE_ID,\n" +
                "                  PARTY_ROLE_NAME\n" +
                "        UNION ALL\n" +
                "        SELECT T.COMPANY_ID,\n" +
                "               T.COMPANY_NAME,\n" +
                "               T.EMPLOYEE_ID PARTY_ROLE_ID,\n" +
                "               T.EMPLOYEE_NAME PARTY_ROLE_NAME,\n" +
                "               T.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT WHERE ID = T.DEPT_ID) DEPT_NAME,\n" +
                "               TO_CHAR(T.TJRQ, 'YYYY-MM-DD') TJRQ_CHAR,\n" +
                "               TJRQ,\n" +
                "               0 YYCZ,\n" +
                "               0 YYCZTC,\n" +
                "               SUM(ROUND(WORK_OUTPUTVALUE_ALL, 2)) AS SGCZ,\n" +
                "               SUM(WASH_AMOUNT) WASH_AMOUNT,\n" +
                "               SUM(SPRAY_PAINT) SPRAY_PAINT,\n" +
                "               SUM(ROUND(WORK_COMMISION_ALL, 2)) AS SGTC\n" +
                "          FROM RPT_WORK_OUTPUT_VALUE T\n" +
                "         GROUP BY TO_CHAR(T.TJRQ, 'YYYY-MM-DD'),\n" +
                "                  TJRQ,\n" +
                "                  COMPANY_ID,\n" +
                "                  COMPANY_NAME,\n" +
                "                  DEPT_ID,\n" +
                "                  DEPT_NAME,\n" +
                "                  EMPLOYEE_ID,\n" +
                "                  EMPLOYEE_NAME) A\n" +
                " WHERE 1 = 1\n" +
                " AND DEPT_ID = :DEPT_ID\n" +
                " AND TJRQ = TO_DATE(:TJRQ,'YYYY-MM-DD')\n" +
                " GROUP BY DEPT_ID, DEPT_NAME, PARTY_ROLE_ID,PARTY_ROLE_NAME\n" +
                " ORDER BY  SUM(A.YYCZ + A.SGCZ) DESC ) T";
        return toJSONArray(find(sql, param));
    }


    //app获取人员产值排名（月）
    public JSONArray getEmpValueRankMon(JSONObject param) {
        String sql = "SELECT T.PARTY_ROLE_ID,T.PARTY_ROLE_NAME,T.DEPT_ID,T.DEPT_NAME,\n" +
                "T.CZ,T.YYCZ,T.SGCZ,T.WASH_AMOUNT,SPRAY_PAINT,ROWNUM,\n" +
                "SUM(T.CZ) OVER (PARTITION BY T.DEPT_ID ) SUMTOTAL FROM  \n" +
                "(SELECT \n" +
                "       A.PARTY_ROLE_ID,\n" +
                "       A.PARTY_ROLE_NAME,\n" +
                "       A.DEPT_ID,\n" +
                "       A.DEPT_NAME,\n" +
                "       SUM(A.YYCZ + A.SGCZ) CZ,\n" +
                "       SUM(A.YYCZ) YYCZ,\n" +
                "       SUM(A.SGCZ) SGCZ,\n" +
                "       SUM(A.WASH_AMOUNT) WASH_AMOUNT,\n" +
                "       SUM(A.SPRAY_PAINT) SPRAY_PAINT\n" +
                "  FROM (SELECT T.COMPANY_ID,\n" +
                "               T.COMPANY_NAME,\n" +
                "               T.PARTY_ROLE_ID,\n" +
                "               T.PARTY_ROLE_NAME,\n" +
                "               T.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT WHERE ID = T.DEPT_ID) DEPT_NAME,\n" +
                "               TO_CHAR(T.TJRQ, 'YYYY-MM-DD') TJRQ_CHAR,\n" +
                "               TJRQ,\n" +
                "                SUM(T.NKXS + DIYNKXS + PTXCXS + XCXS + MRXS + BYXS + LTXS + WXXS + BPXS + GZXS + JYXS + JPXS +\n" +
                "                    SHYPXS + JSJCXS + XXFWXS + JCJSF + FN_COMMISSION +\n" +
                "                    USED_CAR_PRICE + SCZ + SDJ + NSXS) AS YYCZ,\n" +
                "                SUM(T.NKXSTC + DIYNKXSTC + XCXSTC + MRXSTC + BYXSTC + LTXSTC +\n" +
                "                    WXXSTC + BPXSTC + GZXSTC + JYXSTC + JPXSTC + SHYPXSTC +\n" +
                "                    JSJCXSTC + XXFWXSTC + JCJSFTC + NET_PREMIUM_TC +\n" +
                "                    USED_CAR_PRICE_TC + DSGZTC +NSXSTC +KDTC) AS YYCZTC,\n" +
                "               0 SGCZ,\n" +
                "               0 WASH_AMOUNT,\n" +
                "               0 SPRAY_PAINT,\n" +
                "               0 SGTC\n" +
                "          FROM TC_EMP_BUS_OUTPUT_VALUE T\n" +
                "         GROUP BY TO_CHAR(T.TJRQ, 'YYYY-MM-DD'),\n" +
                "                  TJRQ,\n" +
                "                  COMPANY_ID,\n" +
                "                  COMPANY_NAME,\n" +
                "                  DEPT_ID,\n" +
                "                  DEPT_NAME,\n" +
                "                  PARTY_ROLE_ID,\n" +
                "                  PARTY_ROLE_NAME\n" +
                "        UNION ALL\n" +
                "        SELECT T.COMPANY_ID,\n" +
                "               T.COMPANY_NAME,\n" +
                "               T.EMPLOYEE_ID PARTY_ROLE_ID,\n" +
                "               T.EMPLOYEE_NAME PARTY_ROLE_NAME,\n" +
                "               T.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT WHERE ID = T.DEPT_ID) DEPT_NAME,\n" +
                "               TO_CHAR(T.TJRQ, 'YYYY-MM-DD') TJRQ_CHAR,\n" +
                "               TJRQ,\n" +
                "               0 YYCZ,\n" +
                "               0 YYCZTC,\n" +
                "               SUM(ROUND(WORK_OUTPUTVALUE_ALL, 2)) AS SGCZ,\n" +
                "               SUM(WASH_AMOUNT) WASH_AMOUNT,\n" +
                "               SUM(SPRAY_PAINT) SPRAY_PAINT,\n" +
                "               SUM(ROUND(WORK_COMMISION_ALL, 2)) AS SGTC\n" +
                "          FROM RPT_WORK_OUTPUT_VALUE T\n" +
                "         GROUP BY TO_CHAR(T.TJRQ, 'YYYY-MM-DD'),\n" +
                "                  TJRQ,\n" +
                "                  COMPANY_ID,\n" +
                "                  COMPANY_NAME,\n" +
                "                  DEPT_ID,\n" +
                "                  DEPT_NAME,\n" +
                "                  EMPLOYEE_ID,\n" +
                "                  EMPLOYEE_NAME) A\n" +
                " WHERE 1 = 1\n" +
                " AND DEPT_ID = :DEPT_ID\n" +
                " AND TJRQ >= TO_DATE(:TJRQ,'YYYY-MM-DD')\n" +
                " AND TJRQ <= TO_DATE(:TJRQEND,'YYYY-MM-DD')\n" +
                " GROUP BY DEPT_ID, DEPT_NAME, PARTY_ROLE_ID,PARTY_ROLE_NAME\n" +
                " ORDER BY  SUM(A.YYCZ + A.SGCZ) DESC ) T";
        return toJSONArray(find(sql, param));
    }

    //app获取人员提成排名（日）
    public JSONArray getEmpTcRank(JSONObject param) {
        String sql = "SELECT T.PARTY_ROLE_ID,T.PARTY_ROLE_NAME,T.DEPT_ID,T.DEPT_NAME,\n" +
                "T.TC,T.YYCZTC,T.SGTC,ROWNUM,\n" +
                "SUM(T.TC) OVER (PARTITION BY T.DEPT_ID ) SUMTOTAL FROM  \n" +
                "(SELECT \n" +
                "       A.PARTY_ROLE_ID,\n" +
                "       A.PARTY_ROLE_NAME,\n" +
                "       A.DEPT_ID,\n" +
                "       A.DEPT_NAME,\n" +
                "       SUM(A.YYCZTC + A.SGTC) TC,\n" +
                "       SUM(A.YYCZTC) YYCZTC,\n" +
                "       SUM(A.SGTC) SGTC\n" +
                "  FROM (SELECT T.COMPANY_ID,\n" +
                "               T.COMPANY_NAME,\n" +
                "               T.PARTY_ROLE_ID,\n" +
                "               T.PARTY_ROLE_NAME,\n" +
                "               T.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT WHERE ID = T.DEPT_ID) DEPT_NAME,\n" +
                "               TO_CHAR(T.TJRQ, 'YYYY-MM-DD') TJRQ_CHAR,\n" +
                "               TJRQ,\n" +
                "                SUM(T.NKXS + DIYNKXS + PTXCXS + XCXS + MRXS + BYXS + LTXS + WXXS + BPXS + GZXS + JYXS + JPXS +\n" +
                "                    SHYPXS + JSJCXS + XXFWXS + JCJSF + FN_COMMISSION +\n" +
                "                    USED_CAR_PRICE + SCZ + SDJ + NSXS) AS YYCZ,\n" +
                "                SUM(T.NKXSTC + DIYNKXSTC + XCXSTC + MRXSTC + BYXSTC + LTXSTC +\n" +
                "                    WXXSTC + BPXSTC + GZXSTC + JYXSTC + JPXSTC + SHYPXSTC +\n" +
                "                    JSJCXSTC + XXFWXSTC + JCJSFTC + NET_PREMIUM_TC +\n" +
                "                    USED_CAR_PRICE_TC + DSGZTC +NSXSTC +KDTC) AS YYCZTC,\n" +
                "               0 SGCZ,\n" +
                "               0 WASH_AMOUNT,\n" +
                "               0 SPRAY_PAINT,\n" +
                "               0 SGTC\n" +
                "          FROM TC_EMP_BUS_OUTPUT_VALUE T\n" +
                "         GROUP BY TO_CHAR(T.TJRQ, 'YYYY-MM-DD'),\n" +
                "                  TJRQ,\n" +
                "                  COMPANY_ID,\n" +
                "                  COMPANY_NAME,\n" +
                "                  DEPT_ID,\n" +
                "                  DEPT_NAME,\n" +
                "                  PARTY_ROLE_ID,\n" +
                "                  PARTY_ROLE_NAME\n" +
                "        UNION ALL\n" +
                "        SELECT T.COMPANY_ID,\n" +
                "               T.COMPANY_NAME,\n" +
                "               T.EMPLOYEE_ID PARTY_ROLE_ID,\n" +
                "               T.EMPLOYEE_NAME PARTY_ROLE_NAME,\n" +
                "               T.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT WHERE ID = T.DEPT_ID) DEPT_NAME,\n" +
                "               TO_CHAR(T.TJRQ, 'YYYY-MM-DD') TJRQ_CHAR,\n" +
                "               TJRQ,\n" +
                "               0 YYCZ,\n" +
                "               0 YYCZTC,\n" +
                "               SUM(ROUND(WORK_OUTPUTVALUE_ALL, 2)) AS SGCZ,\n" +
                "               SUM(WASH_AMOUNT) WASH_AMOUNT,\n" +
                "               SUM(SPRAY_PAINT) SPRAY_PAINT,\n" +
                "               SUM(ROUND(WORK_COMMISION_ALL, 2)) AS SGTC\n" +
                "          FROM RPT_WORK_OUTPUT_VALUE T\n" +
                "         GROUP BY TO_CHAR(T.TJRQ, 'YYYY-MM-DD'),\n" +
                "                  TJRQ,\n" +
                "                  COMPANY_ID,\n" +
                "                  COMPANY_NAME,\n" +
                "                  DEPT_ID,\n" +
                "                  DEPT_NAME,\n" +
                "                  EMPLOYEE_ID,\n" +
                "                  EMPLOYEE_NAME) A\n" +
                " WHERE 1 = 1\n" +
                " AND DEPT_ID = :DEPT_ID\n" +
                " AND TJRQ = TO_DATE(:TJRQ,'YYYY-MM-DD')\n" +
                " GROUP BY DEPT_ID, DEPT_NAME, PARTY_ROLE_ID,PARTY_ROLE_NAME\n" +
                " ORDER BY  SUM(A.YYCZTC + A.SGTC) DESC ) T";
        return toJSONArray(find(sql, param));
    }

    //app获取人员提成排名（月）
    public JSONArray getEmpTcRankMon(JSONObject param) {
        String sql = "SELECT T.PARTY_ROLE_ID,T.PARTY_ROLE_NAME,T.DEPT_ID,T.DEPT_NAME,\n" +
                "T.TC,T.YYCZTC,T.SGTC,ROWNUM,\n" +
                "SUM(T.TC) OVER (PARTITION BY T.DEPT_ID ) SUMTOTAL FROM  \n" +
                "(SELECT \n" +
                "       A.PARTY_ROLE_ID,\n" +
                "       A.PARTY_ROLE_NAME,\n" +
                "       A.DEPT_ID,\n" +
                "       A.DEPT_NAME,\n" +
                "       SUM(A.YYCZTC + A.SGTC) TC,\n" +
                "       SUM(A.YYCZTC) YYCZTC,\n" +
                "       SUM(A.SGTC) SGTC\n" +
                "  FROM (SELECT T.COMPANY_ID,\n" +
                "               T.COMPANY_NAME,\n" +
                "               T.PARTY_ROLE_ID,\n" +
                "               T.PARTY_ROLE_NAME,\n" +
                "               T.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT WHERE ID = T.DEPT_ID) DEPT_NAME,\n" +
                "               TO_CHAR(T.TJRQ, 'YYYY-MM-DD') TJRQ_CHAR,\n" +
                "               TJRQ,\n" +
                "                SUM(T.NKXS + DIYNKXS + PTXCXS + XCXS + MRXS + BYXS + LTXS + WXXS + BPXS + GZXS + JYXS + JPXS +\n" +
                "                    SHYPXS + JSJCXS + XXFWXS + JCJSF + FN_COMMISSION +\n" +
                "                    USED_CAR_PRICE + SCZ + SDJ + NSXS) AS YYCZ,\n" +
                "                SUM(T.NKXSTC + DIYNKXSTC + XCXSTC + MRXSTC + BYXSTC + LTXSTC +\n" +
                "                    WXXSTC + BPXSTC + GZXSTC + JYXSTC + JPXSTC + SHYPXSTC +\n" +
                "                    JSJCXSTC + XXFWXSTC + JCJSFTC + NET_PREMIUM_TC +\n" +
                "                    USED_CAR_PRICE_TC + DSGZTC +NSXSTC +KDTC) AS YYCZTC,\n" +
                "               0 SGCZ,\n" +
                "               0 WASH_AMOUNT,\n" +
                "               0 SPRAY_PAINT,\n" +
                "               0 SGTC\n" +
                "          FROM TC_EMP_BUS_OUTPUT_VALUE T\n" +
                "         GROUP BY TO_CHAR(T.TJRQ, 'YYYY-MM-DD'),\n" +
                "                  TJRQ,\n" +
                "                  COMPANY_ID,\n" +
                "                  COMPANY_NAME,\n" +
                "                  DEPT_ID,\n" +
                "                  DEPT_NAME,\n" +
                "                  PARTY_ROLE_ID,\n" +
                "                  PARTY_ROLE_NAME\n" +
                "        UNION ALL\n" +
                "        SELECT T.COMPANY_ID,\n" +
                "               T.COMPANY_NAME,\n" +
                "               T.EMPLOYEE_ID PARTY_ROLE_ID,\n" +
                "               T.EMPLOYEE_NAME PARTY_ROLE_NAME,\n" +
                "               T.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT WHERE ID = T.DEPT_ID) DEPT_NAME,\n" +
                "               TO_CHAR(T.TJRQ, 'YYYY-MM-DD') TJRQ_CHAR,\n" +
                "               TJRQ,\n" +
                "               0 YYCZ,\n" +
                "               0 YYCZTC,\n" +
                "               SUM(ROUND(WORK_OUTPUTVALUE_ALL, 2)) AS SGCZ,\n" +
                "               SUM(WASH_AMOUNT) WASH_AMOUNT,\n" +
                "               SUM(SPRAY_PAINT) SPRAY_PAINT,\n" +
                "               SUM(ROUND(WORK_COMMISION_ALL, 2)) AS SGTC\n" +
                "          FROM RPT_WORK_OUTPUT_VALUE T\n" +
                "         GROUP BY TO_CHAR(T.TJRQ, 'YYYY-MM-DD'),\n" +
                "                  TJRQ,\n" +
                "                  COMPANY_ID,\n" +
                "                  COMPANY_NAME,\n" +
                "                  DEPT_ID,\n" +
                "                  DEPT_NAME,\n" +
                "                  EMPLOYEE_ID,\n" +
                "                  EMPLOYEE_NAME) A\n" +
                " WHERE 1 = 1\n" +
                " AND DEPT_ID = :DEPT_ID\n" +
                " AND TJRQ >= TO_DATE(:TJRQ,'YYYY-MM-DD')\n" +
                " AND TJRQ <= TO_DATE(:TJRQEND,'YYYY-MM-DD')\n" +
                " GROUP BY DEPT_ID, DEPT_NAME, PARTY_ROLE_ID,PARTY_ROLE_NAME\n" +
                " ORDER BY  SUM(A.YYCZTC + A.SGTC) DESC ) T";
        return toJSONArray(find(sql, param));
    }


    //生成提成并发死锁重试
    public JSONArray getRetailTcRetry(JSONObject param) {
        String sql = " SELECT T.BILL_ID, T.CON_STR_ID FROM EXE_CONCURRENCY T";
        return toJSONArray(find(sql, param));
    }

    /***
     *  删除原始解析数据
     */
    public int deleteRetailTcRetry(JSONObject param) {
        String sSQL = "DELETE EXE_CONCURRENCY WHERE BILL_ID= :BILL_ID AND CON_STR_ID=:CON_STR_ID ";
        return delete(sSQL, param);
    }

    /***
     *  插入接送车单提成
     */
    public int inserTcOutPickCar(JSONObject param) {
        String sSQL = "INSERT INTO TC_JSCTC (ID,TJRQ,JCRQ,FN_INNER_PICK_DEL_UP_CAR_ID,PLATE_LICENSE,COMPANY_ID,DEPT_ID_MD,\n" +
                "DEPT_ID_JD,JSCR,BZ,TC_AMT) VALUES (IR_TC_JSCTC.NEXTVAL,:TJRQ,:JCRQ,:FN_INNER_PICK_DEL_UP_CAR_ID,:PLATE_LICENSE,:COMPANY_ID,\n" +
                ":DEPT_ID_MD,:DEPT_ID_JD,:JSCR,:BZ,:TC_AMT) ";
        return insert(sSQL, param);
    }


}
